Help Center/ DataArts Fabric/ Developer Guide/ SQL Syntax Reference/ Functions and Operators/ Time and Date Processing Functions and Operators/ date_format
Updated on 2025-09-18 GMT+08:00
View PDF
Share

date_format

date_format(timestamp, fmt)

The date_format function 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 format types that can be used to format date parameters for output. These format types are applicable to the functions date_format, time_format, str_to_date, str_to_time, and from_unixtime.

Table 1 Output formats supported by date_format

Format

Description

Value

%a

Abbreviated week name

Sun...Sat

%b

Abbreviated month name

Jan...Dec

%c

Month

0...12

%D

Day of the month with English suffix

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

%d

Day of a month, two digits

00...31

%e

Day of a month

0...31

%f

Microseconds

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

Minutes

00...59

%j

Day of 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

Full month name

January...December

%m

Month, two digits

00...12

%p

AM or PM

AM PM

%r

Time, in 12-hour format

hh::mm::ss AM/PM

%S

Seconds

00...59

%s

Seconds, same as %S

00...59

%T

Time, in 24-hour format

hh::mm::ss

%U

Week (00–53), Sunday is the first day of the week

00...53

%u

Week (00–53), Monday is the first day of the week

00...53

%V

Week (01–53), Sunday is the first day of the week, used with %X

01...53

%v

Week (01–53), Monday is the first day of the week, used with %x

01...53

%W

Full weekday name

Sunday...Saturday

%w

Day of the week, where Sunday is 0

0...6

%X

Year where Sunday is the first day of the week, 4 digits, used with %V

-

%x

Year where Monday is the first day of the week, 4 digits, used with %v

-

%Y

Year, 4 digits

-

%y

Year, 2 digits

-

%%

Literal '%'

%

%x

'x', any character not listed above

x

Among the output formats supported by date_format, %U, %u, %V, %v, %X, and %x are not currently supported.