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 ...