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.
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.