Issues with Dates

[Chase Zikmund]

Hello, I am trying to figure out this issue with date format. I have a dataset that has 3 dates: admit dob discharge but at some point the dates in the format of a double %tdnn/dd/CCYY turns into scientific notation in the middle of the variable like such:


+------------------------------------+
| admit dob discharge |
|------------------------------------|
62756. | 2/28/2022 6/17/1988 3/3/2022 |
62757. | 2/28/2022 7/22/1967 3/4/2022 |
62758. | 1.962e+12 1.224e+12 1.962e+12 |
62759. | 1.962e+12 1.329e+12 1.967e+12 |
62760. | 1.962e+12 -4.998e+11 1.962e+12 |
|------------------------------------|
62761. | 1.962e+12 1.873e+12 1.962e+12 |
62762. | 1.962e+12 9.779e+11 1.983e+12 |
62763. | 1.962e+12 -1.531e+11 1.962e+12 |
62764. | 1.962e+12 4.549e+11 1.962e+12 |
+------------------------------------+

I noticed that when I use the command "gen dateofbirth = dob" I get this:
+------------------------------------------------+
| admit dob discharge dateofb~h |
|------------------------------------------------|
62756. | 2/28/2022 6/17/1988 3/3/2022 10395 |
62757. | 2/28/2022 7/22/1967 3/4/2022 2759 |
62758. | 1.962e+12 1.224e+12 1.962e+12 1.22e+12 |
62759. | 1.962e+12 1.329e+12 1.967e+12 1.33e+12 |
62760. | 1.962e+12 -4.998e+11 1.962e+12 -5.00e+11 |
|------------------------------------------------|
62761. | 1.962e+12 1.873e+12 1.962e+12 1.87e+12 |
62762. | 1.962e+12 9.779e+11 1.983e+12 9.78e+11 |
62763. | 1.962e+12 -1.531e+11 1.962e+12 -1.53e+11 |
62764. | 1.962e+12 4.549e+11 1.962e+12 4.55e+11 |
+------------------------------------------------+
If that helps.
I am hoping to just have one legible date for the data in question and any time I try to format the variable it reverts everything to the original date in 1960 for stata default state except for the ones in scientific notation ones that convert... somewhat well but they are off by a margin that will not work for accuracy

[Andrew Musau]

Some of these observations look like times and not dates.

. di %tc 1.962e+12
04mar2022 08:00:00

Provide an example using the dataex command as recommended in FAQ Advice #12. For example, you can copy and paste the output of

dataex admit dob discharge in 62756/62764

[Chase Zikmund]

* Example generated by -dataex-. For more info, type help dataex
clear
input double(admit dob discharge)
        22704         10395         22707
        22704          2759         22708
1961712025600 1223899217920 1961971154944
1961714122752 1329048059904 1967414444032
1961721724928 -499780812800 1962057662464
1961727885312 1873454432256 1961798402048
1961782280192  977918427136 1982880022528
1961792897024 -153144000512 1962057662464
1961840082944  454939213824 1962230415360
end
format %tdnn/dd/CCYY dob
format %tdnn/dd/CCYY discharge

here is the results thank your for the quick reply

[Nick Cox]

The same display format for both daily dates and date-times can't work well.

Let's back up a little.

One variable is date of birth. Let's guess that you are unlikely to have a date of birth in the 19th century. So any dates that should be daily dates can be expected to fall between these limits

. di mdy(1,1,1900)
-21914

. di mdy(9, 19, 2023)
23272

The same numbers if they really were datetimes with units ms would fall within about 20 seconds from the start of 1960.

. di %tc mdy(1,1,1900)
31dec1959 23:59:38

. di %tc mdy(9, 19, 2023)
01jan1960 00:00:23

A pragmatic analysis runs that you should be safe interpreting input more than about 25000 as date-times and converting those, and those only, to daily dates.

* Example generated by -dataex-. For more info, type help dataex
clear
input double(admit dob discharge)
        22704         10395         22707
        22704          2759         22708
1961712025600 1223899217920 1961971154944
1961714122752 1329048059904 1967414444032
1961721724928 -499780812800 1962057662464
1961727885312 1873454432256 1961798402048
1961782280192  977918427136 1982880022528
1961792897024 -153144000512 1962057662464
1961840082944  454939213824 1962230415360
end


gen dob2 = cond(abs(dob) > 25000, dofc(dob), dob)

gen discharge2 = cond(abs(discharge) > 25000, dofc(discharge), discharge)

format *2 %td

list  

     +------------------------------------------------------------+
     |     admit          dob   discharge        dob2   dischar~2 |
     |------------------------------------------------------------|
  1. |     22704        10395       22707   17jun1988   03mar2022 |
  2. |     22704         2759       22708   22jul1967   04mar2022 |
  3. | 1.962e+12    1.224e+12   1.962e+12   13oct1998   03mar2022 |
  4. | 1.962e+12    1.329e+12   1.967e+12   11feb2002   06may2022 |
  5. | 1.962e+12   -4.998e+11   1.962e+12   29feb1944   05mar2022 |
     |------------------------------------------------------------|
  6. | 1.962e+12    1.873e+12   1.962e+12   14may2019   02mar2022 |
  7. | 1.962e+12    9.779e+11   1.983e+12   27dec1990   01nov2022 |
  8. | 1.962e+12   -1.531e+11   1.962e+12   23feb1955   05mar2022 |
  9. | 1.962e+12    4.549e+11   1.962e+12   01jun1974   07mar2022 |
     +------------------------------------------------------------+

There is no route to understanding date-times in Stata that does not pass by help datetime

[Chase Zikmund]

That worked, thank you I had to separate some of the data to get it back but I verified it and seems to be all working
Thanks again!