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!
|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
DATE_FORMAT(date,format)Formats the da ...
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 ...
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 ...
MySQL provides CONCAT function to do so. Take an example query: SELECT CONCAT(first_name, \' \', last_name) FROM buyer; ...
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\'); ...
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 ...
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 ...
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 ...
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 ...
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 ...
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
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 ...
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 ...