Converting string to date without any missing values

[Alex Lemarie]

Hello,

I have been trying to convert a string variable first_date into a date format with multiple methods and failing.
When using the command

gen first_date = date(start_date, "DMY")
format first_date %tddd/mm/CCYY

the majority of observations are missing values. Those that are converted are done so wrongly, for instance 9/0/2014 instead of 9/10/2014. I have noticed that the missing values are generated for all dates with double-digit days, for instance 11/25/2014 is a missing value in the date format.

I have checked that the string variable does not have any mistakes in it, and it was imported from an Excel file, in which it was in date format.

Thanks in advance for your help!

[Rich Goldstein]

your "gen" command appears to be incorrect - the examples you give have MDY but your command has DMY

[Alex Lemarie]

Thanks for the suggestion - unfortunately using MDY gives the wrong dates in the first_date variable altogether

[Rich Goldstein]

please read the FAQ and then follow its instructions on providing data examples using -dataex-; otherwise we are just guessing and wasting time

[Igor Paploski]

Hi Alex,

Try:

dataex start_date

and post the output here. If your Stata version does not have dataex, type:

ssc install dataex

Last, but not least, please consider section 12.2 of the FAQ (the portion Rich referred to).

[Alex Lemarie]

Thanks for your help. Here is part of what I get:

input str10 start_date
"  1/3/2016"
"  1/3/2018"
"  1/3/2018"
"  1/3/2018"
"  1/3/2018"
"  1/3/2018"
"  1/4/2014"
"  1/4/2014"
"  1/4/2014"
"  1/4/2014"
"  1/4/2018"
"  1/4/2018"
"  1/4/2018"
"  1/4/2018"
"  1/4/2018"
"  1/4/2018"
"  1/5/2013"
"  1/5/2015"
end

[Rich Goldstein]

well, these are ambiguous but I arbitrarily chose MDY and used the following commands and all looks good to me:

gen st1=daily(start_date,"MDY")
format st1 %td

[Jean-Claude Arbaut]

There is one thing I don't understand:

"I have checked that the string variable does not have any mistakes in it, and it was imported from an Excel file, in which it was in date format."

Excel dates are numbers, and are correctly imported as numbers with a date format by Stata. Why would you have to convert a string then?

In case you want to make it harder than necessary and import as string (with the allstring option of -import excel-), then you end up with MDY dates (I don't think it depends on locale).
So you should not have problems either with date(,"MDY").

Afterthought: I can imagine one case where you can't import directly as number. With some bad files, some (but not all) dates are stored as string. Typically it's either when dates are copy/pasted by hand from a table where they are stored as string, or it's when dates are typed the wrong way (DMY instead of MDY or the other way around, depending on locale settings), and Excel silently stores as string instead of showing an error. In the latter case you are in trouble, as Stata won't recognize the dates, and you can't be sure which dates were correctly entered, you can only identify the trivially bad ones (month>12).

[Alex Lemarie]

It works! I'm guessing that instead of date() I should have been using daily (). Many thanks for your help, Rich and Igor!