Format Functions

This section contains format functions that uses the pattern. The table below contains the supported patterns for the format functions.

Letter for Pattern	Date or Time component
G	Era designator
y or yy or yyyy	Year
M or MM or MMM or MMMM	Month in year
w	Results in week in year
W	Results in week in month
D	Gives the day count in the year
d	Day of the month
F	Day of the week in month
E	Day name in the week
a	AM or PM marker
H	Hour in the day (0-23)
k	Hour in the day (1-24)
m	Minute in the hour
s	Second in the minute

FORMAT_DATE( <expr>, format )

Returns the date expression formatted to the specified format.

> select format_date(cast('2023-02-15' as date), 'MM');
02

FORMAT_TIMESTAMP( <expr>, format )

Return the timestamp formatted according to the specified format

> select format_timestamp(current_timestamp(), 'mm');
2023

This section contains format functions that uses the pattern. The table below contains the supported patterns for the format functions.

Specifier	Description
%a or %W	Abbreviated weekday name (Sun .. Sat) or Weekday name (Sunday .. Saturday)
%b or %M	Abbreviated month name (Jan .. Dec) or Month name (January .. December)
%d or %e	Day of the month, numeric (01 .. 31) or numeric (1 .. 31), this specifier does not support 0 as a month or day.
%c or %m	Month, numeric (1 .. 12), this specifier does not support 0 as a month.
%f	Fraction of second (6 digits for printing: 000000 .. 999000; 1 - 9 digits for parsing: 0 .. 999999999), timestamp is truncated to milliseconds
%H or %k	Hour (00 .. 23)
%h or %I	Hour (01 .. 12)
%j	Day of year (001 .. 366)
%i	Minutes, numeric (00 .. 59)
%S or %s	Seconds (00 .. 59)
%r	Time of day, 12-hour (equivalent to %h:%i:%s %p)
%T	Time of day, 24-hour (equivalent to %H:%i:%s)
%v	Week (01 .. 53), where Monday is the first day of the week and we can use %y or %x for year for using %x use 4 digits for year
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits), when parsing, two-digit year format assumes range 1970 .. 2069, so “70” will result in year 1970 but “69” will produce 2069
%%	A literal % character

DATE_FORMAT( <expr>, format )

Return the date and timestamp formatted according to the specified format

> select date_format(cast('2024-08-26 22:38:11' as timestamp), '%m-%d-%Y %H');
08-26-2024 22