SQL Reference

Formatting Functions

Overview
The format string can be any combination of characters and format patterns for each function specified in the Date and Timestamp Formatting Patterns﻿ and Date and Timestamp Formatting Modifiers﻿ tables.
Patterns can be uppercase or lowercase, but not mixed case. YYYY or yyyy matches YYYY, but not YyYy.
Any characters in the format string that are not part of a pattern match any character in that position in TO_DATE, TO_NUMBER, and TO_TIMESTAMP.
You can escape string literals that contain format patterns with quotes. For example: YYYY"a literal part YYYY" 
Date and Time Formatting Functions
Date and Timestamp Formatting Patterns
Pattern
Description
Y,YYY
4 digit year with comma
YYYY
4 digit year
YYY
Last 3 digits of a year. Values between 000-599 are assumed to be the year 2XXX and 600-999 are assumed to be the year 1XXX.
YY
Last 2 digits of a year. Values between 00-69 are assumed to be the year 20XX and 70-99 are assumed to be 19XX.
Y
Last digit of a year. Assumed to be year 200X.
BC, bc, AD, or ad
An era indicator BC or AD in upper or lower case
B.C., b.c., A.D., or a.d.
An era indicator B.C. or A.D. in upper or lower case
CC
Century value 1-99. Is ignored unless YY, Y, IY, or I are set
MM
Month of the year value 01-12
MONTH
Upper case English month name
Month
English Month name with first letter capitalized
month
lower case English month name
RM
Upper case month of year in Roman numerals
rm
Lower case month of year in Roman numerals
MON
3 letter upper case abbreviation of an English month name
Mon
3 letter abbreviation of an English month name with first letter capitalized
mon
3 letter lower case abbreviation of an English month name
W
Week of month 1-5. For example: Days 15-21 are in the third week of the month.
DD
Day of month 1-31
WW
Week of year 1-53
DDD
Day of year 1-366
D
Day of week 1-7 where Sunday is 1. This value is ignored outside of to_char.
IYYY
4 digit ISO-8601 week-numbered year. See  for more details.
IYY
Last 3 digits of an ISO-8601 week-numbered year. Values between 000-599 are assumed to be the year 2XXX and 600-999 are assumed to be the year 1XXX.
IY
Last 2 digits of an ISO-8601 week-numbered year. Values between 00-69 are assumed to be the year 20XX and 70-99 are assumed to be 19XX.
I
Last digit of an ISO-8601 week-numbered year. Assumed to be the year 200X.
IDDD
Day of an ISO-8601 week-numbered year 001-371
IW
Week of an ISO-8601 week-numbered year 01-53
ID
Day of an ISO-8601 week 01-07 where Monday is 1
HH or HH12
Hour of day 01-12
HH24
Hour of day 0-23
AM, am, PM, or pm
Meridiem indicator AM or PM in upper or lower case
A.M., a.m., P.M., or p.m.
Meridiem indicator A.M. or P.M. in upper or lower case
SSSS
Seconds since midnight 0-86399
MI
Minute of hour 0-59
SS
Second of minute 0-59
MS
Milliseconds 000-999
US
Microseconds 000000-999999
NS
Nanoseconds 000000000-999999999
J
Julian Day (days since November 24, 4714 BC at midnight)
The following modifiers can also be applied to a format pattern.
Date and Timestamp Formatting Modifiers
Modifier
Description
FM
Has no effect on TO_DATE and TO_TIMESTAMP. Will not be considered a literal, but will not otherwise affect the behavior.
TH
Upper case ordinal number suffix. For example: 'DDTH' 
th
Lower case ordinal number suffix. For example: 'DDth' 
FX
Global prefix. By default, TO_DATE and TO_TIMESTAMP collapse all spaces to a single space. If FX is specified, spaces are not collapses. FX must be at the beginning of the format string to be applied. For example: 'FX YYYY-MM-DD' 
TM
Has no effect on TO_DATE and TO_TIMESTAMP. Will not be considered a literal, but will not otherwise affect the behavior.
SP
Has no effect on TO_DATE and TO_TIMESTAMP. Will not be considered a literal, but will not otherwise affect the behavior.
Date and Time notes
You can use time specifiers such as HH12 in TO_DATE. Their format will be validated, but their values will not affect the resulting date.
ISO 8601 formats for dates cannot be mixed with traditional formats for years, months, and days in TO_DATE and TO_TIMESTAMP
Values for MS, US, and NS are scaled up if they do not have leading zeros. TO_TIMESTAMP('0.3', 'S.NS') corresponds to 300000000 NS and TO_TIMESTAMP('0.000000003', 'S.NS') corresponds to 3 NS.
Conflicting information for the same pattern will cause an exception. ex: TO_DATE('05 31', 'DD DD')
Conflicting information across different patterns such as DD and DDD will generally give precedence to the pattern that appears higher in the table.
TO_TIMESTAMP
Converts a character value with the given format to a TIMESTAMP type. 
Syntax
SQL
TO_TIMESTAMP(character_value, character_format)
TO_TIMESTAMP(character_value, character_format)
﻿
Arguments
Data Type
Description
character_value
CHAR
A string of a value to be converted to a TIMESTAMP type. The string must match the provided character_format.
character_format
CHAR
The format pattern to be used to convert the character_value. See the Date and Timestamp Formatting Patterns﻿ section for more information about accepted pattern values. 
Examples
SQL
SELECT TO_TIMESTAMP('2022/12/01', 'YYYY/MM/DD');
SELECT TO_TIMESTAMP('2022/12/01', 'YYYY/MM/DD');
﻿
Output: 2022-12-01 00:00:00.000
SQL
SELECT TO_TIMESTAMP('2023-02-28 13:43:20.403', 'YYYY/MM/DD HH24:MI:SS');
SELECT TO_TIMESTAMP('2023-02-28 13:43:20.403', 'YYYY/MM/DD HH24:MI:SS');
﻿
Output: 2023-02-28 13:43:20.000
TO_DATE
Converts a character value with the specified format to a DATE type. 
Syntax
SQL
TO_DATE(character_value, character_format)
TO_DATE(character_value, character_format)
﻿
Arguments
Data Type
Description
character_value
CHAR
A string of a value to be converted to a DATE type. The string must match the provided character_format.
character_format
CHAR
The format pattern to be used to convert the character_value. See the Date and Timestamp Formatting Patterns﻿ section for more information about accepted pattern values. 
Example
SQL
SELECT TO_DATE('2022/12/01', 'YYYY/MM/DD');
SELECT TO_DATE('2022/12/01', 'YYYY/MM/DD');
﻿
Output: 2022-12-01
Number Formatting Functions
Number Formatting Patterns
Pattern
Description
9
A digit.
0
A digit. In TO_CHAR, includes leading zeros.
. or D
A decimal point.
, or G
A group/thousand separator.
PR
Negative value in braces. For example: -1 = <1>
S or SG
A + or - sign for positive or negative, respectively.
MI
A - sign if the number is negative.
PL
A + sign if the number is positive.
V
Shifts the decimal place to the by the number of digits after V in the format. Cannot be mixed with . or D. For example: TO_NUMBER('32', '9V99') => 0.32
RN
Roman numeral. Not supported by TO_NUMBER.
EEEE
Exponent for scientific notation. Not supported by TO_NUMBER.
L
Currency symbol.
Number Formatting Notes
You cannot mix PR with other sign indicators.
Any character that does not match the corresponding group in the format string is ignored. TO_NUMBER('1&2', '999') = 12.
If PR is present, it must be after every 0 or 9 in the format string.
Similar to TO_DATE and TO_TIMESTAMP, the FM is not considered a literal, but will not otherwise affect the behavior of TO_NUMBER.
TO_NUMBER
Converts a character value with the given format to a DECIMAL type.
Syntax
SQL
TO_NUMBER(character_value, character_format)
TO_NUMBER(character_value, character_format)
﻿
Arguments
Data Type
Description
character_value
CHAR
A string of a value to be converted to a DECIMAL type. The string must match the provided character_format.
character_format
CHAR
The format pattern to be used to convert the character_value. See the Number Formatting Patterns﻿ section for more information about accepted pattern values. 
Example
SQL
SELECT TO_NUMBER('5230.87', '9999V99');
SELECT TO_NUMBER('5230.87', '9999V99');
﻿
Output: 5230.87
Related Links
﻿Date / Time Functions﻿﻿
﻿Time Zone Functions﻿﻿
﻿Character / Binary Functions﻿﻿

Pattern	Description
Y,YYY	4 digit year with comma
YYYY	4 digit year
YYY	Last 3 digits of a year. Values between 000-599 are assumed to be the year 2XXX and 600-999 are assumed to be the year 1XXX.
YY	Last 2 digits of a year. Values between 00-69 are assumed to be the year 20XX and 70-99 are assumed to be 19XX.
Y	Last digit of a year. Assumed to be year 200X.
BC, bc, AD, or ad	An era indicator BC or AD in upper or lower case
B.C., b.c., A.D., or a.d.	An era indicator B.C. or A.D. in upper or lower case
CC	Century value 1-99. Is ignored unless YY, Y, IY, or I are set
MM	Month of the year value 01-12
MONTH	Upper case English month name
Month	English Month name with first letter capitalized
month	lower case English month name
RM	Upper case month of year in Roman numerals
rm	Lower case month of year in Roman numerals
MON	3 letter upper case abbreviation of an English month name
Mon	3 letter abbreviation of an English month name with first letter capitalized
mon	3 letter lower case abbreviation of an English month name
W	Week of month 1-5. For example: Days 15-21 are in the third week of the month.
DD	Day of month 1-31
WW	Week of year 1-53
DDD	Day of year 1-366
D	Day of week 1-7 where Sunday is 1. This value is ignored outside of to_char.
IYYY	4 digit ISO-8601 week-numbered year. See for more details.
IYY	Last 3 digits of an ISO-8601 week-numbered year. Values between 000-599 are assumed to be the year 2XXX and 600-999 are assumed to be the year 1XXX.
IY	Last 2 digits of an ISO-8601 week-numbered year. Values between 00-69 are assumed to be the year 20XX and 70-99 are assumed to be 19XX.
I	Last digit of an ISO-8601 week-numbered year. Assumed to be the year 200X.
IDDD	Day of an ISO-8601 week-numbered year 001-371
IW	Week of an ISO-8601 week-numbered year 01-53
ID	Day of an ISO-8601 week 01-07 where Monday is 1
HH or HH12	Hour of day 01-12
HH24	Hour of day 0-23
AM, am, PM, or pm	Meridiem indicator AM or PM in upper or lower case
A.M., a.m., P.M., or p.m.	Meridiem indicator A.M. or P.M. in upper or lower case
SSSS	Seconds since midnight 0-86399
MI	Minute of hour 0-59
SS	Second of minute 0-59
MS	Milliseconds 000-999
US	Microseconds 000000-999999
NS	Nanoseconds 000000000-999999999
J	Julian Day (days since November 24, 4714 BC at midnight)

Modifier	Description
FM	Has no effect on TO_DATE and TO_TIMESTAMP. Will not be considered a literal, but will not otherwise affect the behavior.
TH	Upper case ordinal number suffix. For example: 'DDTH'
th	Lower case ordinal number suffix. For example: 'DDth'
FX	Global prefix. By default, TO_DATE and TO_TIMESTAMP collapse all spaces to a single space. If FX is specified, spaces are not collapses. FX must be at the beginning of the format string to be applied. For example: 'FX YYYY-MM-DD'
TM	Has no effect on TO_DATE and TO_TIMESTAMP. Will not be considered a literal, but will not otherwise affect the behavior.
SP	Has no effect on TO_DATE and TO_TIMESTAMP. Will not be considered a literal, but will not otherwise affect the behavior.

Arguments	Data Type	Description
character_value	CHAR	A string of a value to be converted to a TIMESTAMP type. The string must match the provided character_format.
character_format	CHAR	The format pattern to be used to convert the character_value. See the Date and Timestamp Formatting Patterns section for more information about accepted pattern values.

Arguments	Data Type	Description
character_value	CHAR	A string of a value to be converted to a DATE type. The string must match the provided character_format.
character_format	CHAR	The format pattern to be used to convert the character_value. See the Date and Timestamp Formatting Patterns section for more information about accepted pattern values.

Pattern	Description
9	A digit.
0	A digit. In TO_CHAR, includes leading zeros.
. or D	A decimal point.
, or G	A group/thousand separator.
PR	Negative value in braces. For example: -1 = <1>
S or SG	A + or - sign for positive or negative, respectively.
MI	A - sign if the number is negative.
PL	A + sign if the number is positive.
V	Shifts the decimal place to the by the number of digits after V in the format. Cannot be mixed with . or D. For example: TO_NUMBER('32', '9V99') => 0.32
RN	Roman numeral. Not supported by TO_NUMBER.
EEEE	Exponent for scientific notation. Not supported by TO_NUMBER.
L	Currency symbol.

Arguments	Data Type	Description
character_value	CHAR	A string of a value to be converted to a DECIMAL type. The string must match the provided character_format.
character_format	CHAR	The format pattern to be used to convert the character_value. See the Number Formatting Patterns section for more information about accepted pattern values.

Updated 16 May 2024

Did this page help you?

Date / Time Functions

HyperLogLog Functions