date and time difficult

[Micah Prochaska]

Hello Statalist, I apologize for posting a date/time question, but I have been unable to find my problem (or the answer).


I have a variable that is a date and time in the format MM/DD/YY HH:MM. It is also a string variable.

des record_dttm

storage display value
variable name type format label variable label
--------------------------------------------------------------------------------------------------------------------------------
record_dttm str14 %14s


list record_dttm in 1/5

+----------------+
| record_dttm |
|----------------|
1. | 10/20/18 11:53 |
2. | 10/20/18 15:14 |
3. | 10/27/17 22:00 |
4. | 10/27/17 22:00 |
5. | 10/27/17 6:30 |
+----------------+

I cannot for the life of me figure out how to create a new variable without the HH:MM.

Here is my code:

. gen tx_day = date(record_dttm, "DDMMYYHHMM")
(6,284 missing values generated)

I have tried every iteration of DMYHHMM that can think of and still not able to format the variable. Again, I apologize for posting a simple date and time issue on this forum.

Best, Micah

[Nick Cox]

Wrong function, date specification, and storage type.

date() is for generating daily dates only. It's not a generic date-time conversion function. You need clock().

For date times you need double type. This advice is emphasised in

help datetime

which offers the only road to success in this territory.

Your dates evidently start MDY, not DMY. Further, it is obvious to you, but not to Stata, that your dates are in the present century. That has to be made explicit. You do realise that your dates start with months, but your code specified otherwise.

For date() or clock(), none of D M Y h m s is ever repeated in the specification. Also h and m (lower case) are essential for hours and minutes. The easiest way to see that is to realise that otherwise Stata has no way to distinguish months and minutes or hours from half-years.

clear
input str13 record_dttm
"10/20/18 11:53"
"10/20/18 15:14"
"10/27/17 22:00"
"10/27/17 22:00"
"10/27/17 6:30"
end

gen double datetime = clock(record_dttm, "MD20Y hm")
format datetime %tc

list

     +------------------------------------+
     |   record_dttm             datetime |
     |------------------------------------|
  1. | 10/20/18 11:5   20oct2018 11:05:00 |
  2. | 10/20/18 15:1   20oct2018 15:01:00 |
  3. | 10/27/17 22:0   27oct2017 22:00:00 |
  4. | 10/27/17 22:0   27oct2017 22:00:00 |
  5. | 10/27/17 6:30   27oct2017 06:30:00 |
     +------------------------------------+

You join a long line of Stata users burnt by date times. Sympathies ... yet at the same time the rules here have to be followed. You have to do exactly what the help tells you to do.

[William Lisowski]

First, some advice on working with Stata dates and times.

Stata's "date and time" variables are complicated and there is a lot to learn. If you have not already read the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF, do so now. If you have, it's time for a refresher. After that, the help datetime documentation will usually be enough to point the way. You can't remember everything; even the most experienced users end up referring to the help datetime documentation or back to the manual for details. But at least you will get a good understanding of the basics and the underlying principles. An investment of time that will be amply repaid.

All Stata manuals are included as PDFs in the Stata installation (since version 11) and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.

OK, on to your problem. The advice is because it's clear that you've confused display formats for Stata Internal Format (SIF) dates and times with the input masks when converting a Human Readable Format (HRF) string to a SIF value. (See help datetime_translation for details on the input masks, it's a clickable link from the section of help datetime dealing with HRF-to-SIF translation. But do the reading given in the advice in any event.) Below is some sample code that demonstrates two ways of obtaining what you want.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 dttm
"10/20/18 11:53"
"10/20/18 15:14"
"10/27/17 22:00"
"10/27/17 22:00"
"10/27/17  6:30"
end

generate double tx_dttm = clock(dttm, "MD20Yhm")
generate tx_day1 = dofc(tx_dttm)
generate tx_day2 = date(dttm, "MD20Y##")

format tx_dttm %tc 
format tx_day1 tx_day2 %tdNN/DD/CCYY
list, clean noobs

. list, clean noobs

              dttm              tx_dttm      tx_day1      tx_day2  
    10/20/18 11:53   20oct2018 11:53:00   10/20/2018   10/20/2018  
    10/20/18 15:14   20oct2018 15:14:00   10/20/2018   10/20/2018  
    10/27/17 22:00   27oct2017 22:00:00   10/27/2017   10/27/2017  
    10/27/17 22:00   27oct2017 22:00:00   10/27/2017   10/27/2017  
    10/27/17  6:30   27oct2017 06:30:00   10/27/2017   10/27/2017

[Micah Prochaska]

Thank you both. I have read these manuals but clearly need to re-read them. Even when reading them they can become confusing. I very much appreciate your help.

[Nick Cox]

In #2 the code should specify str14 to reproduce Micah's example perfectly, but the principles remain the same.