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.
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.
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 12:05:03 2005-12-31 12:05:03 4 xyz 2006-01-31 2006-01-31 12:05:03 2006-01-31 12:05:03
The statement generates output that looks like this:
3 abc 12-31-2005 12-31-2005 12:05:03 12-31-2005 12:05:03 4 xyz 01-31-2006 01-31-2006 12:05:03 01-31-2006 12:05:03
3.15.144.170