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


Parameter

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


Output: 2022-12-01 00:00:00.000

SQL


Output: 2023-02-28 13:43:20.000

TO_DATE

Converts a character value with the specified format to a DATE type.

Syntax

SQL


Parameter

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


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


Parameter

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


Output: 5230.87