Date variable with very long digits values!

[Bader Bin Adwan]

Hi,

I tried to format the date of birth values into something readable with no avail. I tried %td, %tc, and %tC but nothing works correctly. I have also tried to gen new variable with MDY and DMY also give errors. Could you please help to convert the values of Birth_Date into the right format. Please not that when I put the cursor to the first date it show very long digits (1917129600000), I understand it also account the milliseconds, but that is not true, the birth date does not include the time, only date!

* Example generated by -dataex-. For more info, type help dataex
clear
input double Birth_Date
1.9171296e+12
1.8359136e+12
 1.933632e+12
1.9559232e+12
1.2073536e+12
1.9479744e+12
 3.253824e+11
 3.968352e+11
 1.956528e+12
 1.389744e+12
end

[Ken Chui]

How did %tc fail? I got this which seems reasonable:

clear
input bd
1917129600000
end

format bd %tc

list

Results:

     +--------------------+
     |                 bd |
     |--------------------|
  1. | 30sep2020 23:59:48 |
     +--------------------+

If you want to account for the 23 hours 59 mins, see this thread on how to round it: https://www.statalist.org/forums/for...d-time-to-days. It may also be prudent to check the original data to ascertain if it's Sep 30th or Sep 31st.

[Nick Cox]

Something earlier must have prompted Stata to read in these values as datetimes.

There is no road to understanding datetimes that does not go through reading

help datetime

or as much of it as is needed for any specific problem. From that follows a very specific understanding -- expanded at excruciating length in https://journals.sagepub.com/doi/pdf...867X1201200415 -- namely, that changing the display format does not change the value stored, only the display format. You need a conversion function: nothing else will work.

clear
input double Birth_Date
1.9171296e+12
1.8359136e+12
 1.933632e+12
1.9559232e+12
1.2073536e+12
1.9479744e+12
 3.253824e+11
 3.968352e+11
 1.956528e+12
 1.389744e+12
end

gen wanted = dofc(Birth_Date)
format wanted %td 

list, sep(0)

     +-----------------------+
     | Birth_D~e      wanted |
     |-----------------------|
  1. | 1.917e+12   01oct2020 |
  2. | 1.836e+12   06mar2018 |
  3. | 1.934e+12   10apr2021 |
  4. | 1.956e+12   24dec2021 |
  5. | 1.207e+12   05apr1998 |
  6. | 1.948e+12   23sep2021 |
  7. | 3.254e+11   24apr1970 |
  8. | 3.968e+11   29jul1972 |
  9. | 1.957e+12   31dec2021 |
 10. | 1.390e+12   15jan2004 |
     +-----------------------+

[Bader Bin Adwan]

Thanks Ken and Nick.

You are right Nick, I was suspected that something went wrong in reading these values. All I did is import the data from SPSS. While, in SPSS the date values was in correct format, however, in Stata it was not. When I tried (format %tc) it convert into dates and times which is odd, within the new format, some date was correct and many were either preceded or delayed by 1 day!. Thanks again Nick for clarifying, now it works perfectly.