MySQL Cookbook

10.34 Performing Date Conversion Using SQL

10.34.1 Problem

You want to convert dates using SQL statements.

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

10.34.3 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 row as a tab-delimited, linefeed-terminated line.

use strict;
use DBI;

# ... process command-line options (not shown) ...

@ARGV == 2 or die "Usage: $0 [options] db_name tbl_name\n";
my $db_name = shift (@ARGV);
my $tbl_name = shift (@ARGV);

# ... connect to database (not shown) ...

# Read table metadata from MySQL to get colum names and types.  Use the
# types to detect DATE, DATETIME, and TIMESTAMP columns so their contents
# can be rewritten with DATE_FORMAT( ).

my @col;

my $sth = $dbh->prepare ("SHOW COLUMNS FROM $tbl_name");
$sth->execute ( );
while (my @row = $sth->fetchrow_array ( ))
{
    if ($row[1] =~ /^datetime|timestamp/)
    {
        $row[0] = "DATE_FORMAT($row[0], '%m-%d-%Y %T') AS $row[0]";
    }
    elsif ($row[1] =~ /^date/)
    {
        $row[0] = "DATE_FORMAT($row[0], '%m-%d-%Y') AS $row[0]";
    }
    push (@col, $row[0]);
}
my $query = "SELECT\n\t" . join (",\n\t", @col) . "\nFROM $tbl_name";

# Execute SELECT statement and dump out the result

$sth = $dbh->prepare ($query);
$sth->execute ( );
while (my @val = $sth->fetchrow_array ( ))
{
    # convert NULL (undef) values to empty strings
    @val = map { defined ($_) ? $_ : "" } @val;
    print join ("\t", @val) . "\n";
}

$dbh->disconnect ( );

exit (0);

To see how this script works, suppose you have the following table:

CREATE TABLE datetbl
(
    i   INT,
    c   CHAR(10),
    d   DATE,
    dt  DATETIME,
    ts  TIMESTAMP
);

The SELECT statement that the script constructs to export the contents of datetbl looks like this:

SELECT
    i,
    c,
    DATE_FORMAT(d, '%m-%d-%Y') AS d,
    DATE_FORMAT(dt, '%m-%d-%Y %T') AS dt,
    DATE_FORMAT(ts, '%m-%d-%Y %T') AS ts
FROM datetbl

Thus, if datetbl contains the following rows:

3       abc     2001-12-31      2001-12-31 12:05:03     20011231120503
4       xyz     2002-01-31      2002-01-31 12:05:03     20020131120503

The script generates output that looks like this:

3       abc     12-31-2001      12-31-2001 12:05:03     12-31-2001 12:05:03
4       xyz     01-31-2002      01-31-2002 12:05:03     01-31-2002 12:05:03

Going in the other direction (to import non-ISO dates into MySQL), normally you convert the dates to ISO format first. Otherwise, you must import them as character strings, which reduces their usefulness in temporal contexts. However, in some cases, you can import non-ISO dates as strings, then convert them to ISO-format DATE values afterward using SQL statements. Recipe 10.35 shows an example of this technique.

10.34.4 See Also

A variation on the technique of rewriting dates at export time is used in Recipe 10.41, which discusses a mysql_to_filemaker.pl script that exports MySQL tables for use with FileMaker Pro. The script uses DATE_FORMAT( ) to rewrite dates in the MM-DD-CCYY format expected by FileMaker Pro. It also uses DATE_FORMAT( ) to split date-and-time values into separate date and time columns, because FileMaker Pro has no analog for MySQL's DATETIME or TIMESTAMP column types.