---
title: Formatting Functions
slug: mK8dug5AOxRreWtf5PEx1
description: Learn about formatting functions in Ocient to standardize and format data outputs.
docTags: 
createdAt: Thu Jul 21 2022 17:05:13 GMT+0000 (Coordinated Universal Time)
updatedAt: Mon Mar 30 2026 18:52:30 GMT+0000 (Coordinated Universal Time)
---

# 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"`&#x20;

# 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.&#x20;

**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](./#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.&#x20;

**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](./#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](./#number-formatting-patterns) section for more information about accepted pattern values.  |

**Example**

```sql
SELECT TO_NUMBER('5230.87', '9999V99');
```

*Output*: `5230.87`

# Related Links

[Date and Time Functions](<./Date and Time Functions.md>)

[Time Zone Functions](<./Time Zone Functions.md>)

[Character and Binary Functions](<./Character and Binary Functions.md>)

