Dates in Stata imported from SQL Server

[Adam Bunnell]

Hi Stata Community:

I am hoping somebody can assist in the following example using Stata v14.2:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double Appointment_DATE
1.7991072e+12
1.7991072e+12
1.7991072e+12
1.7991072e+12
1.7991072e+12
1.7991072e+12
1.7991072e+12
1.7991072e+12
1.7991072e+12
end
format %tc Appointment_DATE

-------------------------------------------------------------------

The data appear like this in the data browser: "04jan2017 00:00:00".

I would like to create a variable to appear like this: DD/MM/YY. I've searched through the internet, as well as the Statalist/archives.

I appreciate your assistance,

Adam Bunnell

[Carole J. Wilson]

Try the following:

gen double new_date=dofc( Appointment_DATE )
format new_date %tdDD/MM/YY

See: help datetime & help datetime_display_formats

[Adam Bunnell]

Hi Carole,

I used your code and receive this as output:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(Appointment_DATE new_date)
1.7991072e+12 20823
1.7991072e+12 20823
1.7991072e+12 20823
1.7991072e+12 20823
end
format %tc Appointment_DATE
format %tdDD/MM/YY new_date

-----------------------------------------------------------------------------------------
The data in the browser appear very close to what I am looking for.
They appear like this: "04/00/17". The month is not getting picked up.

I appreciate any further suggestions.

Adam

[William Lisowski]

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.

In the terms used by Chapter 24 and help datetime, your dates are SIF clock values. To convert them to SIF daily values, you will want to use the dofc() function. To then display those SIF daily values in the style you want, you will want to apply the %tdDD/NN/YY format. The code would be something like the following.

generate apptdmy = dofc(Appointment_DATE)
format apptdmy %tdDD/NN/YY

  +-------------------------------+
  |   Appointment_DATE    apptdmy |
  |-------------------------------|
  | 04jan2017 00:00:00   04/01/17 |
  +-------------------------------+

I will note that my first attempt also got the format wrong, like Carole I chose the wrong specification for the month, and (even after all this time) I had to consult the references I gave to correct my problem.

[Igor Paploski]

Hi Adam,

Try this:

clear
input double Appointment_DATE
1.7991072e+12
1.7991072e+12
1.7991072e+12
1.7991072e+12
1.7991072e+12
1.7991072e+12
1.7991072e+12
1.7991072e+12
1.7991072e+12
end
format %tc Appointment_DATE
gen double new_date=dofc( Appointment_DATE )
format new_date %tdDD/NN/YY

Explanation: format new_date %tdDD/MM/YY (with double M's) output the minutes in your times. Unfortunately minutes and months both begin with M, so I guess Stata had to choose one be the "m" and the other to be represented by other letter. If you want to format your date output with numeric months, then your format var code will have an (unintuitive) NN or nn. More info here.

[Carole J. Wilson]

Thanks, Igor! You are correct, that should have been "NN" instead of "MM".

[Adam Bunnell]

Hi All,

Thank you for all this information - changing to NN from MM worked perfectly. I have further date manipulation to calculate by querying additional ODBC data sources. The attached information/resources should provide enough information to perform.

Thanks again!

Best,
Adam