	©	SQL :: Manager
				Home	Program Information	Technical details	Free download	Donate for a cup of coffee

The DATE_FORMAT SQL Function in MySQL and MariaDB - Format date and time

DATE_FORMAT	Syntax:	DATE_FORMAT(Date and Time, Format)
	Return value:	CHAR
	Function type:	Date and Time function

Function Description

The DATE_FORMAT() function formats "Date and Time" according to the format string in argument "Format".

The language used for the names is controlled by the value of the lc_time_names system variable.

If the value for the "Date and Time" or the "Format" argument is invalid or NULL, the DATE_FORMAT() function returns NULL.

Options for the "Format" string:

Option	Description
%a	Short weekday name in current locale (Variable lc_time_names).
%b	Short form month name in current locale. For locale en_US this is one of: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov or Dec.
%c	Month with 1 or 2 digits.
%D	Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...).
%d	Day with 2 digits.
%e	Day with 1 or 2 digits.
%f	Microseconds 6 digits.
%H	Hour with 2 digits between 00-23.
%h	Hour with 2 digits between 01-12.
%I	Hour with 2 digits between 01-12.
%i	Minute with 2 digits.
%j	Day of the year (001-366)
%k	Hour with 1 digits between 0-23.
%l	Hour with 1 digits between 1-12.
%M	Full month name in current locale (Variable lc_time_names).
%m	Month with 2 digits.
%p	AM/PM according to current locale (Variable lc_time_names).
%r	Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.
%S	Seconds with 2 digits.
%s	Seconds with 2 digits.
%T	Time in 24 hour format. Short for '%H:%i:%S'.
%U	Week number (00-53), when first day of the week is Sunday.
%u	Week number (00-53), when first day of the week is Monday.
%V	Week number (01-53), when first day of the week is Sunday. Used with %X.
%v	Week number (01-53), when first day of the week is Monday. Used with %x.
%W	Full weekday name in current locale (Variable lc_time_names).
%w	Day of the week. 0 = Sunday, 6 = Saturday.
%X	Year with 4 digits when first day of the week is Sunday. Used with %V.
%x	Year with 4 digits when first day of the week is Sunday. Used with %v.
%Y	Year with 4 digits.
%y	Year with 2 digits.
%#	For str_to_date(), skip all numbers.
%.	For str_to_date(), skip all punctation characters.
%@	For str_to_date(), skip all alpha characters.
%%	A literal % character.

SQL Examples for the DATE_FORMAT function


SELECT date_format(now(), '%Y-%M-%D');
SELECT date_format(now(), '%Y-%m-%d');
SELECT date_format(now(), '%Y-%m-%d %h:%i:%s');
SELECT date_format(now(), '%d.%m.%Y %h:%i:%s');
SELECT date_format(now(), '%h:%i:%s');
SELECT date_format(now(), '%d X %m X %Y %h+%i+%s');

set @form='%d.%m.%Y %h:%i:%s';
SELECT date_format(now(), @form);

SELECT date_format(now(), '');
SELECT date_format(null, '%Y-%M-%D');

date_format(now(), '%Y-%M-%D')
varchar(74) BINARY
2023-April-4th

date_format(now(), '%Y-%m-%d')
varchar(10) BINARY
2023-04-04

date_format(now(), '%Y-%m-%d %h:%i:%s')
varchar(19) BINARY
2023-04-04 11:42:36

date_format(now(), '%d.%m.%Y %h:%i:%s')
varchar(19) BINARY
04.04.2023 11:42:36

date_format(now(), '%h:%i:%s')
varchar(8) BINARY
11:42:36

date_format(now(), '%d X %m X %Y %h+%i+%s')
varchar(23) BINARY
04 X 04 X 2023 11+42+36

date_format(now(), @form)

04.04.2023 11:42:36

date_format(now(), '')
varchar(0) BINARY
NULL

date_format(null, '%Y-%M-%D')
varchar(74) BINARY
NULL

The examples were created with the MyWAY SQL manager: Download

How to use the DATE_FORMAT() function in MySQL and MariaDB databases

In MySQL and MariaDB the function DATE_FORMAT() is used to convert a "Date" or "Date and Time" expression to a specific string representation, allowing the format of the date or datetime to be customized as required. The function provides flexibility in representing dates and times in various user-defined formats. Table column names or other expressions can also be used as parameters to generate specific "Date" or "Date and Time" representations, or to format output and to present dates in a human-readable format, typically in SELECT statements or as Part of the result set presentation.

Further MySQL und MariaDB SQL Date and Time functions

ADDDATE	ADDDATE(Date, [INTERVAL] Number [Unit])
	More about ADDDATE Function

ADDTIME	ADDTIME(Date, Expression)
	More about ADDTIME Function

CONVERT_TZ	CONVERT_TZ(Date, From time zone, To time zone)
	More about CONVERT_TZ Function

CURDATE	CURDATE()
	More about CURDATE Function

CURTIME	CURTIME([Precision])
	More about CURTIME Function

CURRENT_DATE	CURRENT_DATE(-)
	More about CURRENT_DATE Function

CURRENT_TIME	CURRENT_TIME([Precision])
	More about CURRENT_TIME Function

CURRENT_TIMESTAMP	CURRENT_TIMESTAMP([Precision])
	More about CURRENT_TIMESTAMP Function

DATE	DATE(Date and Time)
	More about DATE Function

DATE_ADD	DATE_ADD(Date, INTERVAL Number Unit)
	More about DATE_ADD Function

DATE_SUB	DATE_SUB(Date, INTERVAL Number Unit)
	More about DATE_SUB Function

FROM_DAYS	FROM_DAYS(Number of days)
	More about FROM_DAYS Function

FROM_UNIXTIME	FROM_UNIXTIME(Unix timestamp [, Format])
	More about FROM_UNIXTIME Function

LOCALTIME	LOCALTIME([Precision])
	More about LOCALTIME Function

LOCALTIMESTAMP	LOCALTIMESTAMP([Precision])
	More about LOCALTIMESTAMP Function

MAKEDATE	MAKEDATE(Year, Day of year)
	More about MAKEDATE Function

MAKETIME	MAKETIME(Hours, Minutes, Seconds)
	More about MAKETIME Function

NOW	NOW([Precision])
	More about NOW Function

SEC_TO_TIME	SEC_TO_TIME(Seconds)
	More about SEC_TO_TIME Function

SLEEP	SLEEP(Seconds)
	More about SLEEP Function

STR_TO_DATE	STR_TO_DATE(String, Format)
	More about STR_TO_DATE Function

SUBDATE	SUBDATE(Date, [INTERVAL] Number [Unit])
	More about SUBDATE Function

SUBTIME	SUBTIME(Date and Time, Deduction)
	More about SUBTIME Function

SYSDATE	SYSDATE()
	More about SYSDATE Function

TIME	TIME(Date and Time)
	More about TIME Function

TIMEDIFF	TIMEDIFF(Date 1, Date 2)
	More about TIMEDIFF Function

TIMESTAMP	TIMESTAMP(Date [, Addition])
	More about TIMESTAMP Function

TIMESTAMPADD	TIMESTAMPADD(Unit, Number, Date)
	More about TIMESTAMPADD Function

TIMESTAMPDIFF	TIMESTAMPDIFF(Unit, Date 1, Date 2)
	More about TIMESTAMPDIFF Function

UTC_DATE	UTC_DATE()
	More about UTC_DATE Function

UTC_TIME	UTC_TIME([Precision])
	More about UTC_TIME Function

UTC_TIMESTAMP	UTC_TIMESTAMP([Precision])
	More about UTC_TIMESTAMP Function

DAY	DAY(Date)
	More about DAY Function

DAYOFMONTH	DAYOFMONTH(Date)
	More about DAYOFMONTH Function

DAYOFWEEK	DAYOFWEEK(Date)
	More about DAYOFWEEK Function

DAYOFYEAR	DAYOFYEAR(Date)
	More about DAYOFYEAR Function

EXTRACT	EXTRACT(Unit FROM Date)
	More about EXTRACT Function

HOUR	HOUR(Date and Time)
	More about HOUR Function

MICROSECOND	MICROSECOND(Date and Time)
	More about MICROSECOND Function

MINUTE	MINUTE(Date and Time)
	More about MINUTE Function

MONTH	MONTH(Date)
	More about MONTH Function

QUARTER	QUARTER(Date)
	More about QUARTER Function

SECOND	SECOND(Date and Time)
	More about SECOND Function

TIME_TO_SEC	TIME_TO_SEC(Time)
	More about TIME_TO_SEC Function

TO_DAYS	TO_DAYS(Date)
	More about TO_DAYS Function

TO_SECONDS	TO_SECONDS(Date and Time)
	More about TO_SECONDS Function

UNIX_TIMESTAMP	UNIX_TIMESTAMP([Date and Time])
	More about UNIX_TIMESTAMP Function

WEEK	WEEK(Date [, Mode])
	More about WEEK Function

WEEKDAY	WEEKDAY(Date)
	More about WEEKDAY Function

WEEKOFYEAR	WEEKOFYEAR(Date)
	More about WEEKOFYEAR Function

YEAR	YEAR(Date)
	More about YEAR Function

YEARWEEK	YEARWEEK(Date [, Mode])
	More about YEARWEEK Function

DAYNAME	DAYNAME(Date)
	More about DAYNAME Function

GET_FORMAT	GET_FORMAT(Type, Format)
	More about GET_FORMAT Function

MONTHNAME	MONTHNAME(Date)
	More about MONTHNAME Function

TIME_FORMAT	TIME_FORMAT(Time, Format)
	More about TIME_FORMAT Function

Numeric functions	String functions	Regular Expressions	Date and Time functions
Comparison functions	Encryption & Compression	Conversion functions	NULL functions
Aggregate functions	Window functions	JSON functions	Geometric functions
Sequence functions	Information functions	Dynamic Columns	Miscellaneous functions

More information about the DATE_FORMAT SQL function: mysql.com and mariadb.com and w3schools.com

Updated: 2023-09-24

2020-2024 Heino Cunze-Fischer