MySQL Cookbook

Performing Date Conversion Using SQL

Problem

You want to convert dates using SQL statements.

Solution

For export, use the DATE_FORMAT( ) function to rewrite the values. For import, read the values into a string column and convert them to true DATE values.

Discussion

Suppose you want to export data from MySQL into an application that doesn’t understand ISO-format dates. One way to do this is to export the data into a file, leaving the dates in ISO format. Then run the file through some kind of utility like cvt_date.pl that rewrites the dates into the required format.

Another approach is to export the dates directly in the required format by rewriting them with DATE_FORMAT( ). Suppose you need to export data from a table, but with the dates written in U.S. (MM-DD-CCYY) format. The following script can accomplish this. It takes the names of a database and table as its arguments, then dumps the table in tab-delimited format with the dates in any DATE, DATETIME, or TIMESTAMP columns reformatted. The script does this by examining the table metadata to get the column types, then constructing a SELECT statement that uses DATE_FORMAT( ) to rewrite the dates. Other columns in the table are written without change:

#! /usr/bin/perl -w # iso_to_us.pl - Export a table with dates rewritten from ISO format # (CCYY-MM-DD) to U.S. format (MM-DD-CCYY). This is done by generating a # SELECT statement that selects all the columns of the table, but uses # DATE_FORMAT( ) to rewrite the dates. # Writes each ...