MySQL Cookbook, 2nd Edition

Exporting Dates Using Non-ISO Formats

Problem

You want to export date values using a format other than the ISO (CCYY-MM-DD) format that MySQL uses by default. This might be a requirement when exporting dates from MySQL to applications that don’t understand ISO format.

Solution

Use an external utility to rewrite the dates to non-ISO format after exporting the data from MySQL (cvt_date.pl is useful here). Or use the DATE_FORMAT() function to rewrite the values during the export operation.

Discussion

Suppose that 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 a utility such as 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 that you have the following table:

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

Suppose also that you need to export data from this table, but with the dates in any DATE, DATETIME, or TIMESTAMP columns rewritten in U.S. format (MM-DD-CCYY). A SELECT statement that uses the DATE_FORMAT() function to rewrite the dates as required 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 2005-12-31 2005-12-31 ...