> ## 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

> Reference for Ocient SQL formatting functions to convert values between text and other types using format strings, locales, padding, and precision options.

## 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)