Convert string date time to numerical date time?

[Brad Anderson]

I was sent data in string format which looks like the following:

4/15/2017 8:30:00 AM
6/17/2017 10:31:00 AM
10/30/2017 8:35:00 AM

The AM/PM indicator, seconds indicator and minutes indicator are all in the same position of the string variable. Hours may be either 1 or 2 digits, year is consistently 4 digits but not in consistent columns of the full string variable, months and days may be either 1 or two digits. I know there are string functions that would let me extract data from specific locations in the string. And I'm pretty sure I could eventually write code with conditionals that would extract all of the relevant information. But there are lots of variables like this and this seems like a really kludgy approach. I'm not finding any date/time or string functions that would convert a string variable like this to a numerical variable. Anyone know of a simpler and less error prone strategy? Thanks.

[Andrew Musau]

Looks pretty standard to me:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str21 strtime
"4/15/2017 8:30:00 AM" 
"6/17/2017 10:31:00 AM"
"10/30/2017 8:35:00 AM"
end
gen double wanted = clock(strtime, "MDYhms")
format wanted %tc

Res.:

. l

     +--------------------------------------------+
     |               strtime               wanted |
     |--------------------------------------------|
  1. |  4/15/2017 8:30:00 AM   15apr2017 08:30:00 |
  2. | 6/17/2017 10:31:00 AM   17jun2017 10:31:00 |
  3. | 10/30/2017 8:35:00 AM   30oct2017 08:35:00 |
     +--------------------------------------------+

[Nick Cox]

All your examples are AM but the clock() function is smart about PM too.

. di %tc clock("28 March 2021 10:00:00 AM", "DMY hms")
28mar2021 10:00:00

. di %tc clock("28 March 2021 10:00:00 PM", "DMY hms")
28mar2021 22:00:00

. di %tc clock("28 March 2021 22:00:00 PM", "DMY hms")
28mar2021 22:00:00

What doesn't seem to work is times 24:00 to 24:59 that I've seen people use occasionally for times at and after midnight. You'll have to catch them yourself.

[William Lisowski]

As you've seen, 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.

[Brad Anderson]

Just wanted to say thanks again. I've never worked with data like this and with your help it's working like a charm.