Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

DATE_FORMAT() Function in MariaDB

  • Last Updated : 26 Oct, 2020

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 :

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

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'
My Personal Notes arrow_drop_up
Recommended Articles
Page :