DATE_FORMAT() Function :
In MariaDB, the DATE_FORMAT() function uses two parameters – a date as specified by a format mask. In this function, the first parameter will be a date and the second parameter will be the mask. This function will return the date in the given mask. This function will convert the date with the masking format.
Syntax :
DATE_FORMAT(date, format_mask)
Parameters : Required.
- date – The date to format.
- format_mask – The format to apply to the date.
Returns : The converted date as per the masking format.
Format :
- %Y : Year as a numeric, 4-digit value
- %y : Year as a numeric, 2-digit value
- %a : Weekday name abbreviated (Sun to Sat)
- %b : Month name abbreviated (Jan to Dec)
- %c : Month as a numeric value (0 to 12)
- %D : Day of the month as a numeric value, followed by a suffix (1st, 2nd, 3rd, …)
- %d : Day of the month as a numeric value (01 to 31)
- %e : Day of the month as a numeric value (0 to 31)
- %f : Microseconds (000000 to 999999)
- %H : Hour (00 to 23)
- %h : Hour (00 to 12)
- %I : Hour (00 to 12)
- %i : Minutes (00 to 59)
- %j : Day of the year (001 to 366)
- %k : Hour (00 to 23)
- %l : Hour (1 to 12)
- %M : Month name in full (January to December)
- %m : Month name as a numeric value (00 to 12)
- %p : AM or PM
- %r : Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
- %S : Seconds (00 to 59)
- %s : Seconds (00 to 59)
- %T : Time in 24-hour format (hh:mm: ss)
- %U : Week where Sunday is the first day of the week (00 to 53)
- %u : Week where Monday is the first day of the week (00 to 53)
- %V : Week where Sunday is the first day of the week (01 to 53)
- %v : Week where Monday is the first day of the week (01 to 53)
- %W : Weekday name in full (Sunday to Saturday)
- %w : Day of the week where Sunday=0 and Saturday=6
- %X : Year for the week where Sunday is the first day of the week
- %x : Year for the week where Monday is the first day of the week
Example-1 :
SELECT DATE_FORMAT('2020-04-09', '%M %d, %Y');Output –
'April 09, 2020'
Example-2 :
SELECT DATE_FORMAT('2020-10-18', '%W');Output –
'Sunday'
Example-3 :
SELECT DATE_FORMAT('2020-10-20', '%M %e %Y');Output –
'October 20 2020'
Example-4 :
SELECT DATE_FORMAT('2020-10-19', '%W, %M %e, %Y');Output –
'Monday, October 19, 2020'
Example-5 :
SELECT DATE_FORMAT('2014-05-17 08:44:21.000001', '%h');Output –
8
Example-6 :
SELECT DATE_FORMAT('2019-08-11 10:44:21', '%s');Output –
44
Example-7 :
SELECT DATE_FORMAT('2012-06-15 11:23:16', '%t');Output –
11:23:16
Example-8 :
SELECT DATE_FORMAT('2019-03-13', '%M');Output –
March
Example-9 :
SELECT DATE_FORMAT('2020-10-23', '%W');Output –
Friday
Example-10 :
SELECT DATE_FORMAT('2019-05-13', '%Y');Output –
'2019'