> ## Documentation Index > Fetch the complete documentation index at: https://docs.ocient.com/llms.txt > Use this file to discover all available pages before exploring further. # Formatting Functions ## 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](#date-and-time-formatting-functions) and [Date and Timestamp Formatting Modifiers](#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](https://en.wikipedia.org/wiki/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 SQL theme={null} 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](#date-and-timestamp-formatting-patterns) section for more information about accepted pattern values. | **Examples** ```sql SQL theme={null} SELECT TO_TIMESTAMP('2022/12/01', 'YYYY/MM/DD'); ``` *Output*: `2022-12-01 00:00:00.000` ```sql SQL theme={null} 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 SQL theme={null} 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](#date-and-timestamp-formatting-patterns) section for more information about accepted pattern values. | **Example** ```sql SQL theme={null} 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 SQL theme={null} 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](#number-formatting-patterns) section for more information about accepted pattern values. | **Example** ```sql SQL theme={null} SELECT TO_NUMBER('5230.87', '9999V99'); ``` *Output*: `5230.87` ## Related Links [Date and Time Functions](/date-and-time-functions) [Time Zone Functions](/time-zone-functions) [Character and Binary Functions](/character-and-binary-functions)