Trouble obtaining quarterly values from a date variable

[Brice Karickhoff]

I am starting with a date_of_survey variable that is a string formatted as YYYY-MM-DD. I then run the following commands to convert it to a date variable, and display that variable in a useful format:

gen date = date(date_of_survey, "YMD")
gen date_clean = date
format date_clean %dM_d,_CY
drop date_of_survey

That leaves me with a "date_clean" variable displayed as "September 3, 2020" and a corresponding "date" variable displayed as "22161" (equal to days since January 1, 1960).

I now need to create a variable that indicates the year and quarter of each observation, preferably in YYYY-QQ format. I assumed this shouldn't be difficult, but no matter how I have coded it, I wind up with years in the 7000s and inaccurate quarters. I must be misunderstanding how the dates are stored. My first instinct was to try a simple

format date %tq

command, but I'm still not getting the output I need. Any help is much appreciated. I read over the help files, and can't find the discrepancy that's causing this little problem.

[Brice Karickhoff]

ANSWER: I needed to put the date variable into quarters since January 1, 2021. a qofd() command prior to the format %tq did the trick!

[Nick Cox]

Also cross-posted and answered by yourself at https://stackoverflow.com/questions/...iable-in-stata

Please note our cross-posting policy, which is that you are asked to tell us about it.

Other than years, it's as you say: all Stata date-times are counts of intervals since an origin at the first possible date (date-time) in 1960.

If you have 22161 and tell Stata something like "use display format %td" you're telling Stata to interpret this as a daily date on a scale in which 1 January 1960 is 0. So, Stata will display some version of 3 September 2020, depending precisely on what you specified.

If you have 22161 and tell Stata "use display format %tq" you're telling Stata to interpret this as a quarterly date on a scale in which the first quarter of 1960 is 0. So, Stata will display a quarterly date far into the future.

. di %tq 22161
7500q2

It's not a bug in Stata, but you asked for something you really don't mean.

Much more at https://www.stata-journal.com/articl...article=dm0067 (written because people are often tripped up by this).