Formatting Functions - Ocient Documentation

Formatting Considerations

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 ISO week date 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 after 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 after 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 specified format to a TIMESTAMP type. Syntax

SQL

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');

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');

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)

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');

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 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 specified format to a DECIMAL type. Syntax

SQL

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');

Output: 5230.87 Date and Time Functions Time Zone Functions Character and Binary Functions

​Formatting Considerations

​Date and Time Formatting Functions

​Date and Timestamp Formatting Patterns

​Date and Timestamp Formatting Modifiers

​Date and Time notes

​TO_TIMESTAMP

​TO_DATE

​Number Formatting Functions

​Number Formatting Patterns

​Number Formatting Notes

​TO_NUMBER

​Related Links