> ## Documentation Index
> Fetch the complete documentation index at: https://docs.oxla.com/llms.txt
> Use this file to discover all available pages before exploring further.

# TO_CHAR

## Overview

The `TO_CHAR` function formats various data types, including `date/time`, `integer`, `float point` and `numeric` into a formatted string.

## Syntax

The syntax for using the `TO_CHAR` function is as follows:

<CodeGroup>
  ```sql Timestamp theme={null}
  TO_CHAR(timestamp, format_string)
  ```

  ```sql Interval theme={null}
  TO_CHAR(interval, format_string)
  ```
</CodeGroup>

## Arguments

* `timestamp`: `TIMESTAMP` or `TIMESTAMP WITH TIMEZONE` value to be formatted
* `format`: format of the output string

## Supported Formats

The string format supports the following template patterns (case insensitive):

| **Pattern**                      | **Description**                    |
| -------------------------------- | ---------------------------------- |
| `YYYY`                           | Year (1-9999)                      |
| `MM`                             | Month number (01–12)               |
| `DD`                             | Day of month (01–31)               |
| `HH`                             | Hour of day (1–12)                 |
| `HH12`                           | Hour of day (1–12)                 |
| `HH24`                           | Hour of day (0–23)                 |
| `MI`                             | Minute (0–59)                      |
| `SS`                             | Second (0–59)                      |
| `MS`                             | Millisecond (0–999)                |
| `US`                             | Microsecond (0–999999)             |
| `AM`, `am`, `PM` or `pm`         | Meridiem indicator without periods |
| `A.M.`, `a.m.`, `P.M.` or `p.m.` | Meridiem indicator with periods    |

### General Restrictions

* All text inside double quote `"{text}"` will not be considered a pattern
* The quote character (`"`) will not appear in the result string
* Any text that is not a template pattern is simply copied verbatim i.e. preserved in the result string

### Interval Overflow Restrictions

Interval overflow occurs when an operation involving interval values exceeds the maximum limits of the interval data type,
resulting in an error or unexpected behavior. This can happen when adding, subtracting or multiplying interval values
that lead to a representation that goes beyond the allowable range for any of its components i.e. years, months, days, hours, minutes and seconds.
When executing the `TO_CHAR` function for intervals, it is important to be aware of the following overflow restrictions:

| Conversion      | Source Component | Target Component |
| :-------------- | :--------------: | :--------------: |
| Days to Months  |       Days       |      Months      |
| Hours to Days   |       Hours      |       Days       |
| Seconds to Days |      Seconds     |       Days       |

All in all, for intervals the date overflow doesn't apply (units smaller than an hour can only overflow into hours, but not into days and so on), any excess units will not carry over to the next larger unit.

## Examples

### Intervals

This query converts an interval and displays it in a specified string format:

<CodeGroup>
  ```sql Month_to_Year theme={null}
  SELECT TO_CHAR('25 months'::INTERVAL,'"YEAR:" YYYY "MONTH:" MM') AS FORMATTED_INTERVAL;
  ```

  ```sql Hour_to_Day theme={null}
  SELECT TO_CHAR('13 days' + '49 hours'::INTERVAL, '"Day:" DD "Hour:" HH') AS FORMATTED_INTERVAL;
  ```

  ```sql Second_to_Minute theme={null}
  SELECT TO_CHAR('65 seconds'::INTERVAL, '"MINUTE": MI "SECOND": SS') AS FORMATTED_INTERVAL;
  ```
</CodeGroup>

Here are the outputs for the queries presented above:

<CodeGroup>
  ```sql Month_to_Year theme={null}
            FORMATTED_INTERVAL                
  ---------------------------------------
  YEAR: 0002 MONTH: 01
  ```

  ```sql Hour_to_Day theme={null}
            FORMATTED_INTERVAL                
  ---------------------------------------
  Day: 13 Hour: 01
  ```

  ```sql Second_to_Minute theme={null}
            FORMATTED_INTERVAL                
  ---------------------------------------
  MINUTE: 01 SECOND: 05
  ```
</CodeGroup>

### Timestamps

This query retrieves the current timestamp and displays it in a specified string format:

<CodeGroup>
  ```sql Timestamp theme={null}
  SELECT TO_CHAR(CURRENT_TIMESTAMP(), '"YEAR:" YYYY "MONTH:" MM "DAY:" DD') AS FORMATTED_TIMESTAMP;
  ```

  ```sql Timestamp_with_Microseconds theme={null}
  SELECT TO_CHAR(CURRENT_TIMESTAMP(), 'YYYY-MM-DD HH24:MI:SS.US') AS FORMATTED_TIMESTAMP;
  ```

  ```sql Timestamp_with_Meridiem theme={null}
  SELECT TO_CHAR(CURRENT_TIMESTAMP(), 'YYYY-MM-DD HH12:MI:SS a.m.') AS FORMATTED_TIMESTAMP;
  ```
</CodeGroup>

Here are the outputs for the queries presented above:

<CodeGroup>
  ```sql Timestamp theme={null}
            FORMATTED_TIMESTAMP                
  ---------------------------------------
  YEAR:2025 MONTH:01 DAY:01
  ```

  ```sql Timestamp_with_Microseconds theme={null}
            FORMATTED_TIMESTAMP               
  ---------------------------------------
  2025-01-01 08:08:03.001200 
  ```

  ```sql Timestamp_with_Meridiem theme={null}
            FORMATTED_TIMESTAMP               
  ---------------------------------------
  2025-01-01 08:08:03 p.m. 
  ```
</CodeGroup>