Search code examples
sql-serverdateformatstata

Stata read date variable from MS SQL

I connected Stata via ODBC to a SQL Database.

My problem is Stata reads date variables as Strings. In SQL they have a date format.

How can I import a date variable in SQL as a date in Stata?

Solution

  • we can not answer this question without knowing the format of your date.

    I had a similar problem. If I assume your date is in 

    yyyy-mm-dd

    format, then you need this code (assuming that v1 is the variable that holds your dates in Stata)

     generate v2 = date(v1, "YMD")
format %td v2

    A way that helped me understand the format to use is to print some dates in Stata to see what they look like in Stata's internal encoding: For example:

    display date("2013-08-14", "YMD")
display %td date("2013-08-14", "YMD")

    Now, if you want to convert your data to Stata format because you are getting a type mismatch r(109); error in Stata, execute the following lines of code to get your data in a proper Stata date format:

        . gen str new   = string(value, "%10.0g")
    . gen long date = date(new, "YMD") 
    . format date %td
    . drop new