Trouble Converting SPSS date variable to STATA date!

[Dominique Foster]

I have been at this for days, reading manuals and forum posts, trying to figure out why I'm unable to convert two date variables that I imported into STATA from an SPSS dataset. I'm no closer to the answer than I was when I first began my journey. Any help would be appreciated!

Here is what I know:

I was given an SPSS dataset (.sav) with many variables, two of which are supposed to be timestamps. All I know about these variables is that 1) their data types in SPSS were "Date," 2) SPSS treated them as scale data, and 3) all dates should be between January-May 2019 or January-May 2020. When I bring in the dataset using "import spss..., "the two variables come into STATA as type: double and format: 10.0g. Below is an example of what an observation looks like in the data editor view and the actual number.

Editor View	Actual Number
1.9e+12	1897171200000

I've no clue how to even interpret these numbers to identify what conversion would be necessary. I've tried the code below since I've seen it in so many forums as the solution to the problem. It ends with a blank column.

gen statadate = dofc((spssdate*1000) + tc(14oct1582 00:00))
format statadate %td

Does anyone have any idea of how I might proceed to convert these variables to a date in STATA?

Thanks!

[Nick Cox]

You are in good company in being confused by dates and times, although usually there is nothing except the documentation in

help datetime

to understand. Your sample value

. di %tc 1897171200000
13feb2020 00:00:00

looks fine as a Stata date-time in milliseconds after the start of 1960. Consequently you can extract a daily date by pushing it through dofc()

. di %td dofc(1897171200000) 
13feb2020

and what I show here for display of constants would need to be applied to your variable (spssdate?) . perhaps

gen dailydate = dofc(spssdate) 
format dailydate %td

I haven't used SPSS in the present millennium and never knew anything about how it handles dates and times, but my guess is that your data have been converted fine. Further additions and/or multiplications will just mess them up, which seems to be what you are finding.

[Sergiy Radyakin]

An excellent blog post by Bill Gould may be relevant to understand the conventions, formats and limitations of date and time information storage in different packages:
https://blog.stata.com/2011/01/05/us...ther-software/

If you used -usespss- it offers to adjust the date variables after importing the data, as shown on the screenshot in this thread:
https://www.statalist.org/forums/for...ot-as-expected

SPSS and Stata use different starting points for time, so conversion as in the 1st post is generally necessary: SPSS counts time from the beginning of the Gregorian calendar https://www.historychannel.com.au/th...ys-of-history/ while Stata from Jan 1, 1960. (not sure, perhaps someone's birthday?? though not the author's of the above mentioned blog post).

Best, Sergiy

[Kevin Crow (StataCorp)]

Can you send the SPSS file to [email protected] so that I can look into this problem?

[Dominique Foster]

Nick Cox Thank you! That solved my issue in no time. You just saved my Friday (and likely weekend).