DATE_FORMAT Function

Description

The DATE_FORMAT function converts the value of strings with DATE format ('YYYY-MM-DD' or 'MM/DD/YYYY') or that of date/time data type (DATE, TIMESTAMP, DATETIME) to specified date/time format and then return the value with the VARCHAR data type.

Syntax

DATE_FORMAT(date, format)

Default Date/Time Format

Date/Time Type

Default Output Format

DATE

'MM/DD/YYYY'

TIME

'HH:MI:SS AM'

TIMESTAMP

'HH:MI:SS AM MM/DD/YYYY'

DATETIME

'HH:MI:SS.FF AM MM/DD/YYYY

Date/Time Format 2

format Value

Meaning

%a

Weekday, English abbreviation (Sun, …, Sat)

%b

Month, English abbreviation (Jan, …, Dec)

%c

Month (1, …, 12)

%D

Day of the month, English ordinal number (1st, 2nd, 3rd, ...)

%d

Day of the month, two-digit number (01, …, 31)

%e

Day of the month (1, …, 31)

%f

Microseconds, three-digit number (000, …, 999)

%H

Hour, 24-hour based, number with at least two--digit (00, …, 23, …, 100, …)

%h

Hour, 12-hour based two-digit number (01, …, 12)

%I

Hour, 12-hour based two-digit number (01, …, 12)

%i

Minutes, two-digit number (00, …, 59)

%j

Day of year, three-digit number (001, …, 366)

%k

Hour, 24-hour based, number with at least one-digit (0, …, 23, …, 100, …)

%l

Hour, 12-hour based (1, …, 12)

%M

Month, English string (January, …, December)

%m

Month, two-digit number (01, …, 12)

%p

AM or PM

%r

Time, 12-hour based, hour:minute:second (hh:mm:ss AM or hh:mm:ss PM)

%S

Seconds, two-digit number (00, …, 59)

%s

Seconds, two-digit number (00, …, 59)

%T

Time, 24-hour based, hour:minute:second (hh:mm:ss)

%U

Week, two-digit number, week number of the year with Sunday being the first day Week (00, …, 53)

%u

Week, two-digit number, week number of the year with Monday being the first day (00, …, 53)

%V

Week, two-digit number, week number of the year with Sunday being the first day Week (00, …, 53)
(Available to use in combination with %X)

%v

Week, two-digit number, week number of the year with Monday being the first day (00, …, 53)
(Available to use in combination with %X)

%W

Weekday, English string (Sunday, …, Saturday)

%w

Day of the week, number index (0=Sunday, …, 6=Saturday)

%X

Year, four-digit number calculated as the week number with Sunday being the first day of the week (0000, …, 9999)
(Available to use in combination with %V)  

%x

Year, four-digit number calculated as the week number with Monday being the first day of the week (0000, …, 9999)
(Available to use in combination with %V)

%Y

Year, four-digit number (0001, …, 9999)

%y

Year, two-digit number (00, 01, …,  99)

%%

Output the special character "%" as a string

%x

Output an arbitrary character x as a string out of English letters that are not used as format specifiers.

Example

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');

 date_format('2009-10-04 22:23:00', '%W %M %Y')

======================

  'Sunday October 2009'

 

 

SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');

 date_format('2007-10-04 22:23:00', '%H:%i:%s')

======================

  '22:23:00'

 

SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');

 date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j')

======================

  '4th 00 Thu 04 10 Oct 277'

 

 

SELECT DATE_FORMAT('1999-01-01', '%X %V');

 date_format('1999-01-01', '%X %V')

======================

  '1998 52'