Database Reference
In-Depth Information
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 (see
Recipe 12.12
).
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 US 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
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
12.15. Epilogue
Recall the scenario presented at the beginning of
Chapter 11
:
