Recipe 6.2. Changing MySQL s Date Format

Recipe 6.2. Changing MySQL's Date Format

Problem

You want to change the ISO format that MySQL uses for representing date values.

Solution

You can't. However, you can rewrite non-ISO input values into ISO format when storing dates, and you can rewrite ISO values to other formats for display by using the DATE_FORMAT⁠(⁠ ⁠ ⁠) function.

Discussion

The CCYY-MM-DD format that MySQL uses for DATE values follows the ISO 8601 standard for representing dates. Because the year, month, and day parts have a fixed length and appear left to right in date strings, this format has the useful property that dates sort naturally into the proper temporal order. Chapters Chapter 7 and Chapter 8 discuss ordering and grouping techniques for date-based values.

ISO format, although common, is not used by all database systems, which can cause problems if you want to move data between different systems. Moreover, people commonly like to represent dates in other formats such as MM/DD/YY or DD-MM-CCYY. This too can be a source of trouble, due to mismatches between human expectations of what dates should look like and the way that MySQL actually represents them.

A question frequently asked by people who are new to MySQL is, "How do I tell MySQL to store dates in a specific format such as MM/DD/CCYY?" That's the wrong question. The right question is, "If I have a date in a specific format, how can I store it in MySQL's supported format, and vice versa?" MySQL always stores dates in ISO format, a fact that has implications both for data entry and for processing retrieved query results:

• For data-entry purposes, to store values that are not in ISO format, you normally must rewrite them first. If you don't want to rewrite your dates, you need to store them as strings (for example, in a CHAR column). But then you can't operate on them as dates.

  Chapter 10 covers the topic of date rewriting for data entry. That chapter also discusses checking dates to verify that they're valid. In some cases, if your values are close to ISO format, rewriting may not be necessary. For example, MySQL interprets the string values 87-1-7 and 1987-1-7 and the numbers 870107 and 19870107 as the date 1987-01-07 when they are loaded into a DATE column.

• For display purposes, you can rewrite dates to non-ISO formats by using the DATE_FORMAT⁠(⁠ ⁠ ⁠) function. It provides a lot of flexibility for changing date values into other formats (see later in this section). You can also use functions such as YEAR⁠(⁠ ⁠ ⁠) to extract parts of dates for display (Section 6.6). Additional discussion can be found in Chapter 10.

When you enter date values, one way to rewrite non-ISO dates is to use the STR_TO_DATE⁠(⁠ ⁠ ⁠) function. STR_TO_DATE⁠(⁠ ⁠ ⁠) takes a string representing a temporal value and a format string that specifies the "syntax" of the value. Within the formatting string, you use special sequences of the form % c, where c specifies which part of the date to expect. For example, %Y, %M, and %d signify the four-digit year, the month name, and the two-digit day of the month. To insert the value May 13, 2007 into a DATE column, you can do this:

mysql> INSERT INTO t (d) VALUES(STR_TO_DATE('May 13, 2007','%M %d, %Y')); mysql> SELECT d FROM t; +------------+ | d          | +------------+ | 2007-05-13 | +------------+ 

For date display, MySQL uses ISO format (CCYY-MM-DD) unless you tell it otherwise. If you want to display dates or times in a format other than what MySQL uses by default, use the DATE_FORMAT⁠(⁠ ⁠ ⁠) or TIME_FORMAT⁠(⁠ ⁠ ⁠) functions to rewrite them. If you require a more specialized format that those functions cannot provide, write a stored function.

To rewrite date values into other formats, use the DATE_FORMAT⁠(⁠ ⁠ ⁠) function, which takes two arguments: A DATE, DATETIME, or TIMESTAMP value, and a string describing how to display the value. The format string uses the same kind of specifiers as STR_TO_DATE⁠(⁠ ⁠ ⁠). The following statement shows the values in the date_val table, both as MySQL displays them by default and as reformatted with DATE_FORMAT⁠(⁠ ⁠ ⁠):

mysql> SELECT d, DATE_FORMAT(d,'%M %d, %Y') FROM date_val; +------------+----------------------------+ | d          | DATE_FORMAT(d,'%M %d, %Y') | +------------+----------------------------+ | 1864-02-28 | February 28, 1864          | | 1900-01-15 | January 15, 1900           | | 1987-03-05 | March 05, 1987             | | 1999-12-31 | December 31, 1999          | | 2000-06-04 | June 04, 2000              | +------------+----------------------------+ 

DATE_FORMAT⁠(⁠ ⁠ ⁠) tends to produce rather long column headings, so it's often useful to provide an alias to make a heading more concise or meaningful:

mysql> SELECT d, DATE_FORMAT(d,'%M %d, %Y') AS date FROM date_val; +------------+-------------------+ | d          | date              | +------------+-------------------+ | 1864-02-28 | February 28, 1864 | | 1900-01-15 | January 15, 1900  | | 1987-03-05 | March 05, 1987    | | 1999-12-31 | December 31, 1999 | | 2000-06-04 | June 04, 2000     | +------------+-------------------+ 

The MySQL Reference Manual provides a complete list of format sequences to use with DATE_FORMAT⁠(⁠ ⁠ ⁠), TIME_FORMAT⁠(⁠ ⁠ ⁠), and STR_TO_DATE⁠(⁠ ⁠ ⁠). Some of the more commonly used ones are shown in the following table:

Sequence	Meaning
%Y	Four-digit year
%y	Two-digit year
%M	Complete month name
%b	Month name, initial three letters
%m	Two-digit month of year (01..12)
%c	Month of year (1..12)
%d	Two-digit day of month (01..31)
%e	Day of month (1..31)
%W	Weekday name (Sunday..Saturday)
%r	12-hour time with AM or PM suffix
%T	24-hour time
%H	Two-digit hour
%i	Two-digit minute
%s	Two-digit second
%%	Literal %

The time-related format sequences shown in the table are useful only when you pass DATE_FORMAT⁠(⁠ ⁠ ⁠) a value that has both date and time parts (a DATETIME or TIMESTAMP). The following statement demonstrates how to display DATETIME values from the datetime_val table using formats that include the time of day:

mysql> SELECT dt,     -> DATE_FORMAT(dt,'%c/%e/%y %r') AS format1,     -> DATE_FORMAT(dt,'%M %e, %Y %T') AS format2     -> FROM datetime_val; +---------------------+----------------------+----------------------------+ | dt                  | format1              | format2                    | +---------------------+----------------------+----------------------------+ | 1970-01-01 00:00:00 | 1/1/70 12:00:00 AM   | January 1, 1970 00:00:00   | | 1987-03-05 12:30:15 | 3/5/87 12:30:15 PM   | March 5, 1987 12:30:15     | | 1999-12-31 09:00:00 | 12/31/99 09:00:00 AM | December 31, 1999 09:00:00 | | 2000-06-04 15:45:30 | 6/4/00 03:45:30 PM   | June 4, 2000 15:45:30      | +---------------------+----------------------+----------------------------+ 

TIME_FORMAT⁠(⁠ ⁠ ⁠) is similar to DATE_FORMAT⁠(⁠ ⁠ ⁠), but it understands only time-related specifiers in the format string. TIME_FORMAT⁠(⁠ ⁠ ⁠) works with TIME, DATETIME, or TIMESTAMP values.

mysql> SELECT dt,     -> TIME_FORMAT(dt, '%r') AS '12-hour time',     -> TIME_FORMAT(dt, '%T') AS '24-hour time'     -> FROM datetime_val; +---------------------+--------------+--------------+ | dt                  | 12-hour time | 24-hour time | +---------------------+--------------+--------------+ | 1970-01-01 00:00:00 | 12:00:00 AM  | 00:00:00     | | 1987-03-05 12:30:15 | 12:30:15 PM  | 12:30:15     | | 1999-12-31 09:00:00 | 09:00:00 AM  | 09:00:00     | | 2000-06-04 15:45:30 | 03:45:30 PM  | 15:45:30     | +---------------------+--------------+--------------+ 

If DATE_FORMAT⁠(⁠ ⁠ ⁠) or TIME_FORMAT⁠(⁠ ⁠ ⁠) cannot produce the results that you want, perhaps you can write a stored function that does. Suppose that you want to convert 24-hour TIME values to 12-hour format but with a suffix of a.m. or p.m. rather than AM or PM. The following function accomplishes that task. It uses TIME_FORMAT⁠(⁠ ⁠ ⁠) to do most of the work, and then strips off the suffix supplied by %r and replaces it with the desired suffix:

CREATE FUNCTION time_ampm (t TIME) RETURNS VARCHAR(13) # mm:dd:ss {a.m.|p.m.} format BEGIN   DECLARE ampm CHAR(4);   IF TIME_TO_SEC(t) < 12*60*60 THEN     SET ampm = 'a.m.';   ELSE     SET ampm = 'p.m.';   END IF;   RETURN CONCAT(LEFT(TIME_FORMAT(t, '%r'),9),ampm); END; 

Use the function like this:

mysql> SELECT t1, time_ampm(t1) FROM time_val; +----------+---------------+ | t1       | time_ampm(t1) | +----------+---------------+ | 15:00:00 | 03:00:00 p.m. | | 05:01:30 | 05:01:30 a.m. | | 12:30:20 | 12:30:20 p.m. | +----------+---------------+ 

For more information about writing stored functions, see Chapter 16.