Category Archives: Code

Stata, ODBC and date columns

Stata has a ton of flexilbility for creating and manipulating dates. However, if you want to save Stata data to an external database (e.g. Accesss, PostgreSQL, MySQL, etc.) the numeric date format in Stata will be difficult to interpret outside the program. My use case involved working with Stata to merge and clean some data that was pushed to a MySQL database with odbc and later loaded in R with its odbc functionality. It was in the last step that I learned of Stata’s dating conventions when writing to external databases. So I asked the Stata list.

A very helpful subsciber presented a solution very similar to one that I mocked up. With some of the code posted on the Statalist and some new additions, I present odbc2create. UPDATE: I fixed an issue when dealing with a database with no dates and had to add a loop.

This modified odbc command does the following:

  • searches all your variables for dates (they must be formatted as such or Stata’s ability to detect them is impossible)
  • converts those dates to the YYYY-MM-DD format
  • inserts the dates into your mysql database as strings
  • re-types those date columns in the newly created database as DATEs

The best part: when you load a table created this way back into Stata, it immediately recognized the DATEs as dates.  I hope they build this functionality into Stata in the future.  One caveat (which may explain why they haven’t built it internally) is that the ALTER command in the ado file is specific to MySQL.  Someone should generalize the code to recognize the datasource engine and modify the ALTER command accordingly.