How to convert string date DDMMMYY with MMM characters to Stata dates

[Francois Durant]

Hi,

I would like to convert string date DDMMMYY with MMM characters to Stata dates

Here is a sample of the data:

date
18Mar19
18Mar19
19Mar19
19Mar19
18Mar19
18Mar19
29May19
27May19
16May19
27May19
16Sep19
18Sep19
02Sep19
16Sep19

I have tried to use the following command but the generated variable is empty:
generate date1 = date("date","DDMMMYY")

I have also tried to use the command "todate" with no more success. Please help. Thanks!

[Leonardo Guizzetti]

You will want the function -date()-, as given in an example below.

di %tdCY-m-D date("27May19", "DMY", 2020)

gen want = date(date, "DMY", 2020)
format want %tdCY-m-D

Of course, with only a 2-digit year, you will need to decide an appropriate cutoff date for century. You can read more about internal formats for dates at -help datetime-.

[Francois Durant]

Thank you very much Leonardo, it really helps!

I have looked again in -help datetime-, it actually does not appear (easily at least...). I have looked up "date(" in the documents, none of the command display the syntax of the cutoff for the century.

These date conversion problems are very common. It may be good to incorporate it more clearly in the help file if someone in this forum has control over it!

[Leonardo Guizzetti]

They are clearly documented, but it helps to read from the top and make your way down. The output of -help datetime- discusses how Stata internally represents dates and times, and how this relates to how humans read the same. For syntax specific questions about the date() function, you could search -help date- , or follow one of the first links from -help datetime- called -Datetime Translation-, which will immediately bring you to date() and other related functions, and examples.

[Nick Cox]

todate was alluded to in #1. todate is from SSC, as you are asked to explain (FAQ Advice #12). Its rationale is described in its help:

todate takes run-together date variables consisting of integers or integer characters and generates the corresponding Stata date variables.

Your date input includes alphabetic characters, so todate is indeed irrelevant to your problem. It does not purport to solve it.

The date problem is on all fours with the graph or table problem.

* I have data and want a simple graph. How do I do it?

* I have data and want a simple table. How do I do it?

In each case, the details are what bite. Precisely what you do have and precisely what do you want? People seem curiously reluctant to believe that there won't be many details, depending. We have all internalised since childhood a mass of detailed knowledge about dates, and Stata knows all that, and more, but you still need to focus on specifying details.

I looked again at the help for date(). The optional third argument seems to be documented at length, so the claim in #3 is puzzling.

date(s1,s2[,Y])
Description: the e_d date (days since 01jan1960) corresponding to s1 based on s2 and Y

s1 contains the date, recorded as a string, in virtually any format. Months can be spelled out, abbreviated (to three characters), or
indicated as numbers; years can include or exclude the century; blanks and punctuation are allowed.

s2 is any permutation of M, D, and [##]Y, with their order defining the order that month, day, and year occur in s1. ##, if specified,
indicates the default century for two-digit years in s1. For instance, s2="MD19Y" would translate s1="11/15/91" as 15nov1991.

Y provides an alternate way of handling two-digit years. When a two-digit year is encountered, the largest year, topyear, that does not
exceed Y is returned.

date("1/15/08","MDY",1999) = 15jan1908
date("1/15/08","MDY",2019) = 15jan2008

date("1/15/51","MDY",2000) = 15jan1951
date("1/15/50","MDY",2000) = 15jan1950
date("1/15/49","MDY",2000) = 15jan1949

date("1/15/01","MDY",2050) = 15jan2001
date("1/15/00","MDY",2050) = 15jan2000

If neither ## nor Y is specified, date() returns missing when it encounters a two-digit year. See Working with two-digit years in [D]
datetime translation for more information.
Domain s1: strings
Domain s2: strings
Domain Y: integers 1000 to 9998 (but probably 2001 to 2099)
Range: %td dates 01jan0100 to 31dec9999 (integers -679,350 to 2,936,549) or missing

daily(s1,s2[,Y])
Description: a synonym for date(s1,s2[,Y])