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 :

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 -

Example-6 :

SELECT DATE_FORMAT('2019-08-11 10:44:21', '%s');

Output -

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'

ADDDATE() and ADDTIME() Function in MariaDB

vipinyadav15799

Improve

Article Tags :

SQL
DBMS-SQL

DATE_FORMAT() Function in MariaDB

Similar Reads

Thank You!

What kind of Experience do you want to share?