Converts a value of type DATE, TIMESTAMP, or TIMESTAMPTZ to a formatted string.

​
Syntax

TO_CHAR(<expression>, '<format>')

​
Parameters

ParameterDescriptionSupported input types
<expression>A date or time expression to be converted to text.DATE, TIMESTAMP, TIMESTAMPTZ
<format>A string literal that specifies the format of the <expression> to convert.See below.

Accepted <format> patterns include:

Format optionDescriptionExample
HH12 or HHHour of day (01–12)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'hh12 HH'); --> '06 06'
HH24Hour of day (00–23)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'HH24'); --> '18'
MIMinute (00–59)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'MI'); --> '24'
SSSecond (00–59)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'SS'); --> '58'
SSSS or SSSSSSeconds past midnight (0–86399)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'SSSS'); --> '41098'
MSMillisecond (000–999)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'MS'); --> '085'
USMicrosecond (000000–999999)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'US'); --> '085109'
Y,YYYYear (4 or more digits) with comma Y,YYYTO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'Y,YYY'); --> '2,023'
YYYYYear (4 or more digits)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'YYYY'); --> '2023'
YYYLast 3 digits of yearTO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'YYY'); --> '023'
YYLast 2 digits of yearTO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'YY'); --> '23'
YLast digit of yearTO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'Y'); --> '3'
IYYYISO 8601 week-numbering year (4 or more digits)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'IYYY'); --> '2023'
IYYLast 3 digits of ISO 8601 week-numbering yearTO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'IYY'); --> '023'
IYLast 2 digits of ISO 8601TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'IY'); --> '23'
ILast digit of ISO 8601 week-numbering yearTO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'I'); --> '3'
MMMonth number (01–12)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'MM'); --> '03'
DDDDay of year (001–366)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'DDD'); --> '062'
DDDay of month (01–31)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'DD'); --> '03'
DDay of the week, Sunday (1) to Saturday (7)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'D'); --> '6'
IDISO 8601 day of the week, Monday (1) to Sunday (7)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'ID'); --> '5'
WWeek of month (1–5) (the first week starts on the first day of the month)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'W'); --> '1'
WWWeek number of year (1–53) (the first week starts on the first day of the year)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'WW'); --> '09'
IWWeek number of ISO 8601 week-numbering year (01–53) (the first Thursday of the year is in week 1)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'IW'); --> '09'
CCCentury (2 digits) (the twenty-first century starts on 2001-01-01)TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'CC'); --> '21'
QSingle digit quarterTO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'Q'); --> '1'
AM or PMMeridiem indicator upper case without periodsTO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'hhAM'); --> '6PM'
am or pmMeridiem indicator lower case without periodsTO_CHAR(TIMESTAMPTZ '2023-03-03 09:24:58', 'hhpm'); --> '9am'
A.M. or P.M.Meridiem indicator upper case with periodsTO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58', 'hhP.M.'); --> '6P.M.'
a.m. or p.m.Meridiem indicator lower case with periodsTO_CHAR(TIMESTAMPTZ '2023-03-03 09:24:58', 'hha.m.'); --> '9a.m.'
MONTHFull upper case month name (blank-padded to 9 chars)TO_CHAR(DATE ‘2023-03-03’, ‘MONTH’); —> ‘MARCH    ‘
MonthFull capitalized month name (blank-padded to 9 chars)TO_CHAR(DATE ‘2023-08-07’, ‘Month’); —> ‘August   ‘
monthFull lower case month name (blank-padded to 9 chars)TO_CHAR(DATE '2023-09-10', 'month'); --> 'September'
MONAbbreviated upper case month name (3 chars)TO_CHAR(DATE '2023-07-03', 'MON'); --> 'JUL'
MonAbbreviated capitalized month name (3 chars)TO_CHAR(DATE '2023-01-03', 'Mon'); --> 'Jan'
monAbbreviated lower case month name (3 chars)TO_CHAR(DATE '2023-12-03', 'mon'); --> 'dec'
DAYFull upper case day name (blank-padded to 9 chars)TO_CHAR(DATE ‘2023-03-07’, ‘DAY’); —> ‘TUESDAY  ‘
DayFull capitalized day name (blank-padded to 9 chars)TO_CHAR(DATE ‘2023-03-08’, ‘Day’); —> ‘Wednesday’
dayFull lower case day name (blank-padded to 9 chars)TO_CHAR(DATE ‘2023-03-09’, ‘day’); —> ‘thursday ‘
DYAbbreviated upper case day name (3 chars in English)TO_CHAR(DATE '2023-03-09', 'DY'); --> 'THU'
DyAbbreviated capitalized day name (3 chars in English)TO_CHAR(DATE '2023-03-10', 'Dy'); --> 'Fri'
dyAbbreviated lower case day name (3 chars in English)TO_CHAR(DATE '2023-03-11', 'dy'); --> 'sat'
RMMonth in upper case Roman numerals (I–XII; I=January)TO_CHAR(DATE '2023-03-03', 'RM'); --> 'III'
rmMonth in lower case Roman numerals (i–xii; i=January)TO_CHAR(DATE '2023-06-03', 'rm'); --> 'vi'
AD or BCUpper case era indicator without periodsTO_CHAR(DATE '2023-03-03', 'BC'); --> 'AD'
ad or bclower case era indicator without periodsTO_CHAR(DATE '2023-03-03', 'ad'); --> 'ad'
A.D. or B.C.Upper case era indicator with periodsTO_CHAR(DATE '2023-03-03', 'A.D.'); --> 'A.D.'
a.d. or b.c.Upper case era indicator with periodsTO_CHAR(DATE '2023-03-03', 'b.c.'); --> 'a.d.'
TZUpper case time-zone abbreviationSET timezone = 'America/Vancouver';
TO_CHAR(TIMESTAMPTZ '2023-03-03', 'TZ'); --> 'PST'
tzLower case time-zone abbreviationSET timezone = 'Europe/Berlin';
TO_CHAR(TIMESTAMPTZ '2023-03-03', 'tz'); --> 'cet'
TZHTime zone hoursSET TIMEZONE = 'Israel';
TO_CHAR(TIMESTAMPTZ '2023-03-03', 'TZH'); --> '+02'
TZMTime zone minutesSET TIMEZONE = 'Israel';
TO_CHAR(TIMESTAMPTZ '2023-03-03', 'tzm'); --> '00'
OFTime zone offset from UTCSET TIMEZONE = 'America/New_York';
TO_CHAR(TIMESTAMPTZ '2023-03-03', 'OF'); --> '-08:00'

Additionally, modifiers can be applied to the format patterns above to alter their behavior.

Format optionDescriptionExample
FM prefixSurpress leading zeroes and padding blanksTO_CHAR(CURRENT_DATE, ‘Month YYYY’); —> ‘March     2023’
TO_CHAR(CURRENT_DATE, 'FMMonth YYYY'); --> 'March 2023'
TH suffixUpper case ordinal number suffixTO_CHAR(CURRENT_TIMESTAMP, 'MMTH'); --> '1ST'
th suffixLower case ordinal number suffixTO_CHAR(CURRENT_TIMESTAMP, 'MMth'); --> '3rd'

Any character in the format string that is not recognized as a pattern is simply copied over without being replaced. Parts that are quoted " will be copied over independent of possibly valid patterns. Patterns are matched in lower and upper case if there is no other behavior described above.

​
Return Types

Formatted string based on the function’s specifications

​
Examples

The example below outputs the current local time in a formatted string. Note that the " around the words Date and Time are required, otherwise the characters D and I would be interpreted as valid patterns which would result in the output 6ate and T3me.

The following example outputs the current date in a formatted string with any time field set to 0 which indicates midnight. Note the quotation marks again that are required to prevent unintended replacements: