MySQL Database learning



Home / Funtions and Operators / What is MySQL date format and its specifiers?
Share this job:


What is MySQL date format and its specifiers?

I need to know the method and its specifiers to get MySQL date in required format(s). Is there any list of MySQL date format specifier, I will be pleased!
DATE_FORMAT(date,format)
Formats the date value according to the format string. The following specifiers may be used in the format string:

Specifier Description
%M Month name (January..December)
%W Weekday name (Sunday..Saturday)
%D Day of the month with English suffix (1st, 2nd, 3rd, etc.)
%Y Year, numeric, 4 digits
%y Year, numeric, 2 digits
%X Year for the week where Sunday is the first day of the week, numeric, 4 digits, used with '%V'
%x Year for the week, where Monday is the first day of the week, numeric, 4 digits, used with '%v'
%a Abbreviated weekday name (Sun..Sat)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%m Month, numeric (01..12)
%c Month, numeric (1..12)
%b Abbreviated month name (Jan..Dec)
%j Day of year (001..366)
%H Hour (00..23)
%k Hour (0..23)
%h Hour (01..12)
%I Hour (01..12)
%l Hour (1..12)
%i Minutes, numeric (00..59)
%r Time, 12-hour (hh:mm:ss [AP]M)
%T Time, 24-hour (hh:mm:ss)
%S Seconds (00..59)
%s Seconds (00..59)
%p AM or PM
%w Day of the week (0=Sunday..6=Saturday)
%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'
%% A literal `%'.

All other characters are just copied to the result without interpretation:

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); 
-> 'Saturday October 1997'
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s'); -> '22:23:00'
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
SELECT DATE_FORMAT('1999-01-01', '%X %V'); -> '1998 52'
As of MySQL Version 3.23, the `%' character is required before format specifier characters. In earlier versions of MySQL, `%' was optional.



Funtions and Operators related other questions

What is MySQL date format and its specifiers?

DATE_FORMAT(date,format)Formats the da ...

How to get comma separated all values in a single row against each ID?

You can get all values in a single row in MySQL using its GROUP_CONCAT function. Try as follow:SELECT p_id,GROUP_CONCAT(participant_answer ORDER BY p_id SEPARATOR \',\') AS participant_answers ...

How does union work in MySQL?

The UNION is used to combine two or more separate queries by placing it between them to return a single result. The syntax of UNION operator is as below:SELECT col_1, col_2, .... UNION [ALL | DIS ...

How to concat values in MySQL?

MySQL provides CONCAT function to do so. Take an example query: SELECT CONCAT(first_name, \' \', last_name) FROM buyer; ...

How to convert time zone in MySQL?

MySQL Server allows to convert time zone using the CONVERT_TZ() function, which performs time zone conversions of datetime values:SELECT CONVERT_TZ(\'2005-01-27 13:30:00\', \'+01:00\', \'+03:00\'); ...

How to set conditions on query data with CASE construct?

Control flow functions enable you to choose between different values based on the result of an expression. The CASE construct is not a function, but it too provides flow control. It has two forms of syntax. T ...

What are comparison functions in MySQL

Comparison functions enable you to test relative values or membership of one value within a set of values. LEAST() and GREATEST() take a set of values as arguments and return the one that is smallest or large ...

How to set control conditions on query data with IF funtion?

Control flow functions enable you to choose between different values based on the result of an expression. IF() tests the expression in its first argument and returns its second or third argument depending ...

How are aggregate functions in MySQL?

Aggregate functions perform summary operations on a set of values, such as counting, averaging, or finding minimum or maximum values. Aggregate functions often are used in conjunction with a GROUP BY clause t ...

What are mathematical functions in MySQL?

Numeric functions perform several types of operations, such as rounding, truncation, trigonometric calculations, or generating random numbers. The ROUND() function performs rounding of its argument. T ...

What are string functions in MySQL?

String functions calculate string lengths, extract pieces of strings, search for substrings or replace them, perform lettercase conversion, and more. LENGTH() and CHAR_LENGTH() functions The ...

What are temporal or date time functions in MySQL?

Temporal functions perform operations such as extracting parts of dates and times, reformatting values, or converting values to seconds or days. In many cases, a temporal function that takes a date or time ar ...

What are NULL value related functions in MySQL?

Functions intended specifically for use with NULL values include ISNULL() and IFNULL().  ISNULL() is true if its argument is NULL and false otherwise: mysql> SELECT ISNULL(NULL), ISNULL(0), I ...