Format Functions

Modify the appearance of dates, numbers, and text values

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