Issue with converting date in dd.mm.yy format to dd/mm/yyyy

[Shen YANG]

I have a historical dataset with teenagers' birthday in the format of dd.mm.yy, while they were all born after 1980.

e.g.
15.03.88
02.06.95
...

The variable name is: dob

I wrote the following syntax, but returned with empty values, it seems nothing has been converted:

gen dob_new = date(dob, "DMY")
format dob_new %tdDD/MM/YYYY

I also tried the following:

gen dob_num = clock(substr(dob, 1, 2) + "/" + substr(dob, 4, 2) + "/20" + substr(dob, 7, 2), "DMY")

It works but I don't know how to convert it back to dd/mm/yyyy format.
Could anyone please help?

[Jared Greathouse]

Please use dataex to show us your data. Welcome to Statalist!!!

[Clyde Schechter]

The first problem is that your original variable has two-digit years, so Stata doesn't know what century they belong to. Since you have said they are birthdates, I feel safe in assuming that they are always 2023 or earlier. Consequently we can do this:

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 dob
"15.03.88"
"02.06.95"
end

gen dob_new = date(dob, "DMY", 2023)
assert missing(dob_new) == missing(dob)

Then there is another problem. In datetime display formats, MM does not stand for month, it stands for minutes. There are a few ways to designate months in datetime display formats; the one you are looking for is NN. Additionally, the representation for a four digit year is not YYYY (which would just duplicate the two low order digits of the year) but CCYY.

format dob_new %tdDD/NN/CCYY

Working with date variables in Stata is complicated. If this project working with dates is not a one-off, you should invest time in reading the chapters in the PDF documentation that comes with your Stata installation regarding these matters. You can reach those easily by looking at -help date- and -help datetime display functions-. In each case there will be a blue link at the top of the page that will take you directly to those chapters. It is a lot to read, and you won't remember all of the details--nobody does. But it will give you the framework of how this all works and a sense of the possibilities. Then, whenever you use Stata dates and run into problems, reading the corresponding -help- file will enable you to fill in the little details like the ones that tripped you up here.

In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Shen YANG]

Hi Clyde, many thanks for the detailed reply!