MySQL Date and Time Formatting Details
Date and Time in MySQL - Part 6
Forward: In this part of the series, I explain the details concerning the date and time formatting in MySQL.
By: Chrysanthus Date Published: 4 Sep 2012
Introduction
I have talk about some date formatting in the previous parts of the series. However, what about the situation when you would want to represent the month 12 by text such as Dec or December, and the day of the week, 1 with Sun or Sunday? It is that type of things that I explain in this part of the series.
Formatting Demonstration
Assume that you want the date, '2009-09-04' to appear (be returned) as follows:
Friday September 2009
Note that the day number of the month has not been displayed even though it is given in '2009-09-04'. The result, “Friday September 2009” (displayed) is alright. To achieve that, you will use the following statement:
SELECT date_format('2009-09-04', '%W %M %Y');
This statement uses the DATE_FORMAT() function. This function takes two arguments: the first argument is the date or datetime value. The second argument, also as a string in quotes is what determines the formatting. You need to learn how to code this argument. This argument receives what is known as specifiers. The one above has three specifiers, which are %W, %M and %Y.
Here, the specifier, %W stands for a weekday name e.g. Friday; the specifier, %M stands for month name e.g. September; the specifier, %Y stands for a four-digit numeric year e.g. 2009. Note that in the second argument, %W has been typed first so that Friday should appear first in the output; %M has been typed next so that September should appear next in the output; %Y has been typed last so that 2009 should appear last at the output. There is a specifier for the month number, but you are not obliged to type it in the second argument, in this case.
Also note that there is a space between Friday and September and a space between September and 2009, in the formatted output. The space between Friday and September is the space between %W and %M in the second argument. The space between September and 2009 is the space between %M and %Y in the second argument.
Specifiers and their Meanings
Specifier : Meaning
%a : Abbreviated weekday name (Sun..Sat)
%b : Abbreviated month name (Jan..Dec)
%c : Month, numeric (0..12)
%D : Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d : Day of the month, numeric (00..31)
%e : Day of the month, numeric (0..31)
%f : Microseconds (000000..999999)
%H : Hour (00..23)
%h : Hour (01..12)
%I : Hour (01..12)
%I : Minutes, numeric (00..59)
%j : Day of year (001..366)
%k : Hour (0..23)
%l : Hour (1..12)
%M : Month name (January..December)
%m : Month, numeric (00..12) – two digits
%p : AM or PM
%r : Time, 12-hour (hh:mm:ss followed by AM or PM)
%S : Seconds (00..59)
%s : Seconds (00..59)
%T : Time, 24-hour (hh:mm:ss)
%U : Week (00..53), where Sunday is the first day of the week
%u : Week (00..53), where Monday is the first day of the week
%V : Week (01..53), where Sunday is the first day of the week; used with %X
%v : Week (01..53), where Monday is the first day of the week; used with %x
%W : Weekday name (Sunday..Saturday)
%w : Day of the week (0=Sunday..6=Saturday)
%X : Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x : Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y : Year, numeric, four digits
%y : Year, numeric (two digits)
%% : A literal “%” character
%x : x, for any “x” not listed above
Note that each specifier consists of a percentage (%) sign and an alphabet in upper or lower case.
The DATE_FORMAT function is used to output a properly formatted date. The syntax is:
DATE_FORMAT(date,format)
There are two arguments separated by a comma. Each argument is a string in quotes. The first argument is a date or datetime type. The second argument is a string in quotes consisting of speifiers at positions (first, second, third, etc.) where you want the corresponding proper item to appear in the output. Any separation characters such as space, forward slash, comma or even text that are typed in the format argument, appear at the output at the same positions. I now give you some examples.
If you want the following output:
Sunday the 23rd of March 2014
you would type,
SELECT date_format('2014-03-23', '%W the %D of %M %Y');
Note the use and positions of the particular specifiers (from the list above) in the second argument. Also note the proper items and their corresponding positions to the specifiers at the output. The texts, “the” and “of” in the format (second) argument of the function have appeared at their same positions in the output.
If you want the following output:
Sun, 23rd Mar 2014
you would type,
SELECT date_format('2014-03-23', '%a, %D %b %Y');
Note that the comma in the second argument appears in the output. All specifiers used in the format argument are from the list above.
You can convert the time part of a datetime type to a 12-hour AM/PM format. This needs the specifier, %l for the hour part of the time format. The format argument also needs the extra %p specifier at the end of the format argument. For the date argument, you need the datetime type; only the time type (part) would not be accepted by the function. The statement:
SELECT date_format('2007-11-04 22:13:35', '%l:%i:%s %p');
outputs,
10:13:35 PM
The function ignores the date part of the datetime type of the first argument. However, that date part has to be written. The colons in the format argument appears at the output. If you want dots to appear at the output in place of the colons, replace the colons in the format argument with dots. All specifiers used, come from the above list.
At the end of the day not much formatting is done with the time type as it is with the date type.
As a last example of date formatting, if you want the output:
Sunday the 23rd of March 2014 at 21 Hrs, 42 Min and 33 Sec
You would type,
SELECT date_format('2014-03-23 21:42:33', '%W the %D of %M %Y at %H Hrs, %i Min and %s Sec');
All specifiers used come from the above list.
Note: You normally will not know the particular weekday (e.g. Sunday, or Monday, or Tuesday, etc) that will appear at the output. However, the date_format() function determines that for you.
In the U.S.A you write the date beginning with the month, then the day of the month and then the year, e.g. 11-23-2012. In Britain, you write the date beginning with the day of the month, then the month and then the year, e.g. 23-11-2012. Time also has variations in formatting but the time variations are not many. Simple formatting for date, datetime and time has names. The following list gives the names as arguments to the GET_FORMAT() function. The meanings of the names are also given.
Function Call : Meaning
GET_FORMAT(DATE, 'USA') : '%m.%d.%Y'
GET_FORMAT(DATE, 'JIS') : '%Y-%m-%d'
GET_FORMAT(DATE, 'ISO') : '%Y-%m-%d'
GET_FORMAT(DATE, 'EUR') : '%d.%m.%Y'
GET_FORMAT(DATE, 'INTERNAL') : '%Y%m%d'
GET_FORMAT(DATETIME, 'USA') : '%Y-%m-%d %H.%i.%s'
GET_FORMAT(DATETIME, 'JIS') : '%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME, 'ISO') : '%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME, 'EUR') : '%Y-%m-%d %H.%i.%s'
GET_FORMAT(DATETIME, 'INTERNAL') : '%Y%m%d%H%i%s'
GET_FORMAT(TIME, 'USA') : '%h:%i:%s %p'
GET_FORMAT(TIME, 'JIS') : '%H:%i:%s'
GET_FORMAT(TIME, 'ISO') : '%H:%i:%s'
GET_FORMAT(TIME, 'EUR') : '%H.%i.%s'
GET_FORMAT(TIME, 'INTERNAL') : '%H%i%s'
In the list the name USA, when applied to DATE outputs the format, '%m.%d.%Y'. The name, INTERNATIONAL when applied to DATETIME outputs the format, '%Y%m%d%H%i%s'.
The GET_FORMAT() function is similar to the DATE_FORMAT() function. However, the GET_FORMAT() function deals only with a specific number of basic formats. It needs the formatting name combined with the type (DATE, DATETIME or TIME) as argument, in order to output a specific format. On the other hand the DATE_FORMAT() function allows you all the flexibility and so all kinds of formatting output.
Try the following get_format function statement where the get_format function call is inside the date_format function call:
SELECT date_format('2004-10-03', get_format(DATE,'EUR'));
What the get_format() function returns, becomes the format argument for the date_format() function.
The output is 03.10.2004, beginning with the day of the month, then month and then year. The separation character is the dot and not the hyphen or space for the EUR formatting name. Note how the GET_FORMAT function has been written in the position of the second (format) argument of the DATE_FORMAT function. The GET_FORMAT function is a simple way to obtain the format specifiers of the format argument for the DATE_FORMAT function.
Try the following statement for the USA formatting name combined with datetime type:
SELECT date_format('2004-10-03 08:43:59', get_format(DATETIME,'USA'));
The output is, 2004-10-03 08.43.59
Try the following statement for the INTERNATIONAL formatting name combined with the TIME data type:
SELECT date_format('2004-10-03 08:43:59',get_format(DATETIME,'INTERNAL'));
The output is 20041003084359 without any character separations (I do not recommend the use of this formatting).
Note that the data type (DATE, DATETIME or TIME) as the first argument in the GET_FORMAT function is not in quotes.
Remember, the first argument of the DATE_FORMAT function is the default DATE or DATETIME or TIME type for MySQL, in quotes. Now, read through the list again to properly understand the use of the GET_FORMAT function.
So the DATE_FORMAT function is used without the GET_FORMAT function, when you want to do the formatting yourself. When you want a basic predetermined format, use the GET_FORMAT function inside the DATE_FORMAT function.
Well, we have had a long ride for this part of the series. Let us take a break here and continue in the next part.
Chrys