DATE_FORMAT() Function in MariaDB

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'