Import format for date/time variables

[John Welton]

I have a large dataset delimited by | that includes date time stamps for several variables that are bar coded data. There are nurses administering medications in a hospital, e.g. time/date var1 is time med due, var2 when med pulled from dispensing cart, var3 time med was actually administered. Ultimately we will calculate time differences between these variables. We are working to import these data, approximately 1.5 million records into Stata. Our first test import would not recognize the date/times below and imported them as text in the same format as below. What is the appropriate format in the source data for the source time/date variables that will allow Stata to directly import these as date/time not text data? I would like to avoid coding each of the variables to change them to date/time.

849|2014-11-07 16:13:00.000|2014-11-07 16:12:00.000|2014-11-07 16:13:00.000
852|2014-11-07 16:13:00.000|2014-11-07 16:12:00.000|2014-11-07 16:13:00.000
1379|2014-11-07 16:13:00.000|2014-11-07 16:12:00.000|2014-11-07 16:13:00.000
848|2014-11-07 16:13:00.000|2014-11-07 16:12:00.000|2014-11-07 16:13:00.000

Thanks, John
University of Colorado College of Nursing

[Nick Cox]

We can't comment on your code because you don't show any. I don't see any information either on what kind of file you are reading from.

This worked for me as a matter of working on a string variable within Stata. Note that your example is ambiguous as between 11 July and 7 November. I jumped one way capriciously. If you know that your data are YDM you can change code accordingly.

clear 
input str80 data
"849|2014-11-07 16:13:00.000|2014-11-07 16:12:00.000|2014-11-07 16:13:00.000"
"852|2014-11-07 16:13:00.000|2014-11-07 16:12:00.000|2014-11-07 16:13:00.000"
"1379|2014-11-07 16:13:00.000|2014-11-07 16:12:00.000|2014-11-07 16:13:00.000"
"848|2014-11-07 16:13:00.000|2014-11-07 16:12:00.000|2014-11-07 16:13:00.000"
end 
split data, p(|) destring 

forval j = 2/4 { 
    gen double time`j' = clock(data`j', "YMD hms")
    format time`j' %tc 
} 

     +--------------------------------------------------------------+
     |              time2                time3                time4 |
     |--------------------------------------------------------------|
  1. | 07nov2014 16:13:00   07nov2014 16:12:00   07nov2014 16:13:00 |
  2. | 07nov2014 16:13:00   07nov2014 16:12:00   07nov2014 16:13:00 |
  3. | 07nov2014 16:13:00   07nov2014 16:12:00   07nov2014 16:13:00 |
  4. | 07nov2014 16:13:00   07nov2014 16:12:00   07nov2014 16:13:00 |
     +--------------------------------------------------------------+