Help Center/ Data Warehouse Service/ SQL Syntax Reference (9.1.0.x)/ Functions and Operators/ Date and Time Processing Functions and Operators/ date_format
Updated on 2025-09-18 GMT+08:00
View PDF

date_format

date_format(timestamp, fmt)

Converts a date into a string in the format specified by fmt.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
SELECT date_format('2009-10-04 22:23:00', '%M %D %W');
    date_format
--------------------
 October 4th Sunday
(1 row)
SELECT date_format('2021-02-20 08:30:45', '%Y-%m-%d %H:%i:%S');
     date_format
---------------------
 2021-02-20 08:30:45
(1 row)
SELECT date_format('2021-02-20 18:10:15', '%r-%T');
     date_format
----------------------
 06:10:15 PM-18:10:15
(1 row)

Table 1 describes the patterns of date parameter values. They can be used for the date_format, time_format, str_to_date, str_to_time, and from_unixtime functions.

Table 1 Formats for the output string

Format

Description

Value

%a

Abbreviated week name

Sun...Sat

%b

Abbreviated month name

Jan...Dec

%c

Month

0...12

%D

Date with a suffix

0th, 1st, 2nd, 3rd, ...

%d

Day in a month (two digits)

00...31

%e

Day in a month

0...31

%f

Microsecond

000000...999999

%H

Hour, in 24-hour format

00...23

%h

Hour, in 12-hour format

01...12

%I

Hour, in 12-hour format, same as %h

01...12

%i

Minute

00...59

%j

Day in a year

001...366

%k

Hour, in 24-hour format, same as %H

0...23

%l

Hour, in 12-hour format, same as %h

1...12

%M

Month name

January...December

%m

Month (two digits)

00...12

%p

Morning and afternoon

AM PM

%r

Time, in 12-hour format

hh::mm::ss AM/PM

%S

Second

00...59

%s

Second, same as %S

00...59

%T

Time, in 24-hour format

hh::mm::ss

%U

Week (Sunday is the first day of a week.)

00...53

%u

Week (Monday is the first day of a week.)

00...53

%V

Week (Sunday is the first day of a week). It is used together with %X.

01...53

%v

Week (Monday is the first day of a week). It is used together with %x.

01...53

%W

Week name

Sunday...Saturday

%w

Day of a week. The value is 0 for Sunday.

0...6

%X

Year (four digits). It is used together with %V. Sunday is the first day of a week.

-

%x

Year (four digits). It is used together with %v. Monday is the first day of a week.

-

%Y

Year (four digits)

-

%y

Year (two digits)

-

%%

Character '%'

Character '%'

%x

'x': any character apart from the preceding ones

Character 'x'

In the preceding table, %U, %u, %V, %v, %X, and %x are not supported currently.

Parent Topic: Date and Time Processing Functions and Operators