CODEWITHSUNDEEP
datetimestata
EconoQ
EconoQ

Reputation: 13

Days between dates calculation

I imported date variables as strings from SQL (date1) into Stata and then created a new date variable (date2) like this:

gen double date2 = clock(date1, "YMDhms")
format date2 %tc

However, now I want to calculate the number of days between two dates (date3-date2), formatted as above, but I can't seem to do it.

I don't care about the hms, so perhaps I should strip that out first? And then deconstruct the date into YYYY MM DD as separate variables? Nothing I seems to do is working right now.

Upvotes: 0

Views: 1298

Answers (1)

dimitriy
dimitriy

Reputation: 9460

It sounds like by dates you actually mean timestamp (aka datetime) variables. In my experience, there's usually no need to cast dates/timestamps as strings since ODBC and Stata will handle the conversion to SIF td/tc formats nicely.

But perhaps you exported to a text file and then read in the data instead. Here are a couple solutions.

tc timestamps are in milliseconds since 01jan1960 00:00:00.000, assuming 1000*60*60*24=86,400 seconds/day (that is, ignoring leap seconds). This means that you need to divide your difference by that number to get elapsed days.

For example, 2016 was a leap year:

. display (tc(01jan2017 00:00:00) - tc(01jan2016 00:00:00))/(1000*60*60*24)
366

You can also use the dofc() function to make dates out of timestamps and omit the division:

. display (dofc(tc(01jan2018 00:00:00)) - dofc(tc(01jan2016 00:00:00)))
731

2017 is not a leap year, so 366 + 365 = 731 days.

You can use generate with all these functions, though display is often easier for debugging initial attempts.

Upvotes: 1

Related Questions