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




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net