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

> Reference material for TO_CHAR function

# TO_CHAR

export const QueryWindow = ({content}) => {
  const {sql, result} = content;
  const [inited, setInited] = useState(false);
  const buttonRef = useRef(null);
  useEffect(() => {
    if (!inited && buttonRef.current) {
      runQuery(buttonRef.current, true);
      setInited(true);
    }
  }, []);
  return <div className="query-window">
      <div className="query-toolbar">
        <button className="run-button" onClick={ev => runQuery(ev.target)} ref={buttonRef}>Run Query</button>
        <span className="window-title">Interactive SQL Playground 🔥</span>
      </div>
      <div className="query-content">
        <pre><code className="firebolt-sql language-sql" contentEditable="true" spellCheck="false" data-original-query={sql}>{sql}</code></pre>
        <script type="application/json" className="fallback-result" style={{
    display: "none"
  }}>{JSON.stringify(result)}</script>
        <div className="server-unavailable-banner query-window-hidden">
          The Firebolt playground server is currently unavailable. Using precomputed query results.
        </div>
        <div className="query-results"></div>
      </div>
    </div>;
};

Converts a value of type `DATE`, `TIMESTAMP`, or `TIMESTAMPTZ` to a formatted string.

## Syntax

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
TO_CHAR(<expression>, '<format>')
```

## Parameters

| Parameter      | Description                                                                  | Supported input types              |
| :------------- | :--------------------------------------------------------------------------- | :--------------------------------- |
| `<expression>` | A date or time expression to be converted to text.                           | `DATE`, `TIMESTAMP`, `TIMESTAMPTZ` |
| `<format>`     | A string literal that specifies the format of the `<expression>` to convert. | See below.                         |

Accepted `<format>` patterns include:

| Format option     | Description                                                                                       | Example                                                                                           |
| :---------------- | :------------------------------------------------------------------------------------------------ | :------------------------------------------------------------------------------------------------ |
| `HH12` or `HH`    | Hour of day (01–12)                                                                               | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'hh12 HH'); --> '06 06'`                       |
| `HH24`            | Hour of day (00–23)                                                                               | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'HH24'); --> '18'`                             |
| `MI`              | Minute (00–59)                                                                                    | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'MI'); --> '24'`                               |
| `SS`              | Second (00–59)                                                                                    | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'SS'); --> '58'`                               |
| `SSSS` or `SSSSS` | Seconds past midnight (0–86399)                                                                   | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'SSSS'); --> '41098'`                          |
| `MS`              | Millisecond (000–999)                                                                             | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'MS'); --> '085'`                              |
| `US`              | Microsecond (000000–999999)                                                                       | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'US'); --> '085109'`                           |
| `Y,YYY`           | Year (4 or more digits) with comma Y,YYY                                                          | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'Y,YYY'); --> '2,023'`                         |
| `YYYY`            | Year (4 or more digits)                                                                           | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'YYYY'); --> '2023'`                           |
| `YYY`             | Last 3 digits of year                                                                             | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'YYY'); --> '023'`                             |
| `YY`              | Last 2 digits of year                                                                             | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'YY'); --> '23'`                               |
| `Y`               | Last digit of year                                                                                | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'Y'); --> '3'`                                 |
| `IYYY`            | ISO 8601 week-numbering year (4 or more digits)                                                   | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'IYYY'); --> '2023'`                           |
| `IYY`             | Last 3 digits of ISO 8601 week-numbering year                                                     | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'IYY'); --> '023'`                             |
| `IY`              | Last 2 digits of ISO 8601                                                                         | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'IY'); --> '23'`                               |
| `I`               | Last digit of ISO 8601 week-numbering year                                                        | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'I'); --> '3'`                                 |
| `MM`              | Month number (01–12)                                                                              | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'MM'); --> '03'`                               |
| `DDD`             | Day of year (001–366)                                                                             | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'DDD'); --> '062'`                             |
| `DD`              | Day of month (01–31)                                                                              | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'DD'); --> '03'`                               |
| `D`               | Day of the week, Sunday (1) to Saturday (7)                                                       | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'D'); --> '6'`                                 |
| `ID`              | ISO 8601 day of the week, Monday (1) to Sunday (7)                                                | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'ID'); --> '5'`                                |
| `W`               | Week of month (1–5) (the first week starts on the first day of the month)                         | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'W'); --> '1'`                                 |
| `WW`              | Week number of year (1–53) (the first week starts on the first day of the year)                   | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'WW'); --> '09'`                               |
| `IW`              | Week number of ISO 8601 week-numbering year (01–53) (the first Thursday of the year is in week 1) | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'IW'); --> '09'`                               |
| `CC`              | Century (2 digits) (the twenty-first century starts on 2001-01-01)                                | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'CC'); --> '21'`                               |
| `Q`               | Single digit quarter                                                                              | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'Q'); --> '1'`                                 |
| `AM` or `PM`      | Meridiem indicator upper case without periods                                                     | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58.085109', 'hhAM'); --> '6PM'`                            |
| `am` or `pm`      | Meridiem indicator lower case without periods                                                     | `TO_CHAR(TIMESTAMPTZ '2023-03-03 09:24:58', 'hhpm'); --> '9am'`                                   |
| `A.M.` or `P.M.`  | Meridiem indicator upper case with periods                                                        | `TO_CHAR(TIMESTAMPTZ '2023-03-03 18:24:58', 'hhP.M.'); --> '6P.M.'`                               |
| `a.m.` or `p.m.`  | Meridiem indicator lower case with periods                                                        | `TO_CHAR(TIMESTAMPTZ '2023-03-03 09:24:58', 'hha.m.'); --> '9a.m.'`                               |
| `MONTH`           | Full upper case month name (blank-padded to 9 chars)                                              | <code>TO\_CHAR(DATE '2023-03-03', 'MONTH'); --> 'MARCH    '</code>                                |
| `Month`           | Full capitalized month name (blank-padded to 9 chars)                                             | <code>TO\_CHAR(DATE '2023-08-07', 'Month'); --> 'August   '</code>                                |
| `month`           | Full lower case month name (blank-padded to 9 chars)                                              | `TO_CHAR(DATE '2023-09-10', 'month'); --> 'September'`                                            |
| `MON`             | Abbreviated upper case month name (3 chars)                                                       | `TO_CHAR(DATE '2023-07-03', 'MON'); --> 'JUL'`                                                    |
| `Mon`             | Abbreviated capitalized month name (3 chars)                                                      | `TO_CHAR(DATE '2023-01-03', 'Mon'); --> 'Jan'`                                                    |
| `mon`             | Abbreviated lower case month name (3 chars)                                                       | `TO_CHAR(DATE '2023-12-03', 'mon'); --> 'dec'`                                                    |
| `DAY`             | Full upper case day name (blank-padded to 9 chars)                                                | <code>TO\_CHAR(DATE '2023-03-07', 'DAY'); --> 'TUESDAY  '</code>                                  |
| `Day`             | Full capitalized day name (blank-padded to 9 chars)                                               | <code>TO\_CHAR(DATE '2023-03-08', 'Day'); --> 'Wednesday'</code>                                  |
| `day`             | Full lower case day name (blank-padded to 9 chars)                                                | <code>TO\_CHAR(DATE '2023-03-09', 'day'); --> 'thursday '</code>                                  |
| `DY`              | Abbreviated upper case day name (3 chars in English)                                              | `TO_CHAR(DATE '2023-03-09', 'DY'); --> 'THU'`                                                     |
| `Dy`              | Abbreviated capitalized day name (3 chars in English)                                             | `TO_CHAR(DATE '2023-03-10', 'Dy'); --> 'Fri'`                                                     |
| `dy`              | Abbreviated lower case day name (3 chars in English)                                              | `TO_CHAR(DATE '2023-03-11', 'dy'); --> 'sat'`                                                     |
| `RM`              | Month in upper case Roman numerals (I–XII; I=January)                                             | `TO_CHAR(DATE '2023-03-03', 'RM'); --> 'III'`                                                     |
| `rm`              | Month in lower case Roman numerals (i–xii; i=January)                                             | `TO_CHAR(DATE '2023-06-03', 'rm'); --> 'vi'`                                                      |
| `AD` or `BC`      | Upper case era indicator without periods                                                          | `TO_CHAR(DATE '2023-03-03', 'BC'); --> 'AD'`                                                      |
| `ad` or `bc`      | lower case era indicator without periods                                                          | `TO_CHAR(DATE '2023-03-03', 'ad'); --> 'ad'`                                                      |
| `A.D.` or `B.C.`  | Upper case era indicator with periods                                                             | `TO_CHAR(DATE '2023-03-03', 'A.D.'); --> 'A.D.'`                                                  |
| `a.d.` or `b.c.`  | Upper case era indicator with periods                                                             | `TO_CHAR(DATE '2023-03-03', 'b.c.'); --> 'a.d.'`                                                  |
| `TZ`              | Upper case time-zone abbreviation                                                                 | `SET timezone = 'America/Vancouver';`<br />`TO_CHAR(TIMESTAMPTZ '2023-03-03', 'TZ'); --> 'PST'`   |
| `tz`              | Lower case time-zone abbreviation                                                                 | `SET timezone = 'Europe/Berlin';`<br />`TO_CHAR(TIMESTAMPTZ '2023-03-03', 'tz'); --> 'cet'`       |
| `TZH`             | Time zone hours                                                                                   | `SET TIMEZONE = 'America/Chicago';`<br />`TO_CHAR(TIMESTAMPTZ '2023-03-03', 'TZH'); --> '-06'`    |
| `TZM`             | Time zone minutes                                                                                 | `SET TIMEZONE = 'America/Chicago';`<br />`TO_CHAR(TIMESTAMPTZ '2023-03-03', 'tzm'); --> '00'`     |
| `OF`              | Time zone offset from UTC                                                                         | `SET TIMEZONE = 'America/New_York';`<br />`TO_CHAR(TIMESTAMPTZ '2023-03-03', 'OF'); --> '-08:00'` |

Additionally, modifiers can be applied to the format patterns above to alter their behavior.

| Format option | Description                                | Example                                                                                                                                   |
| :------------ | :----------------------------------------- | :---------------------------------------------------------------------------------------------------------------------------------------- |
| `FM` prefix   | Suppress leading zeroes and padding blanks | <code>TO\_CHAR(CURRENT\_DATE, 'Month YYYY'); --> 'March     2023'</code> <br /> `TO_CHAR(CURRENT_DATE, 'FMMonth YYYY'); --> 'March 2023'` |
| `TH` suffix   | Upper case ordinal number suffix           | `TO_CHAR(CURRENT_TIMESTAMP, 'MMTH'); --> '1ST'`                                                                                           |
| `th` suffix   | Lower case ordinal number suffix           | `TO_CHAR(CURRENT_TIMESTAMP, 'MMth'); --> '3rd'`                                                                                           |

Any character in the format string that is not recognized as a pattern is simply copied over without being replaced. Parts that are quoted `"` will be copied over independent of possibly valid patterns.
Patterns are matched in lower and upper case if there is no other behavior described above.

## Return Types

Formatted string based on the function's specifications

## Examples

The example below outputs the current local time in a formatted string.
Note that the `"` around the words `Date` and `Time` are required, otherwise
the characters `D` and `I` would be interpreted as valid patterns which would
result in the output `6ate` and `T3me`.

<QueryWindow
  content={{
"sql": "SELECT TO_CHAR(\n    TIMESTAMPTZ '2023-03-02 06:33:26.466511',\n    '\"Date\": FMMonth FMddth, YYYY \"Time\": FMHH12am (mi:ss.us) OF (TZ)'\n);",
"result": {
"data": [
  [
    "Date: March 2nd, 2023 Time: 6am (33:26.466511) +00 (UTC)"
  ]
],
"meta": [
  {
    "name": "?column?",
    "type": "text"
  }
],
"query": {
  "query_id": "ef85a568-9f41-4d87-a3eb-ddf382e8353b",
  "query_label": null,
  "request_id": "0d162027-4795-4004-a2b3-0df6903a9764"
},
"rows": 1,
"statistics": {
  "bytes_read": 1,
  "elapsed": 0.006857,
  "rows_read": 1,
  "scanned_bytes_cache": 0,
  "scanned_bytes_storage": 0,
  "time_before_execution": 0.000367652,
  "time_to_execute": 9.4084e-05
}
}
}}
/>

The following example outputs the current date in a formatted string with
any time field set to `0` which indicates midnight. Note the quotation marks
again that are required to prevent unintended replacements:

<QueryWindow
  content={{
"sql": "SELECT TO_CHAR(\n    DATE '2023-03-02' ,\n    '\"The\" fmDDDth \"day in\" YY \"is a\" fmDay \"at midnight\" hh24:mi:ss.us'\n);",
"result": {
"data": [
  [
    "The 61st day in 23 is a Thursday at midnight 00:00:00.000000"
  ]
],
"meta": [
  {
    "name": "?column?",
    "type": "text"
  }
],
"query": {
  "query_id": "29316dac-4378-4b82-a78c-aa4e8a24af84",
  "query_label": null,
  "request_id": "9febd769-0883-490e-a4a6-8dd11a08ea61"
},
"rows": 1,
"statistics": {
  "bytes_read": 1,
  "elapsed": 0.0067,
  "rows_read": 1,
  "scanned_bytes_cache": 0,
  "scanned_bytes_storage": 0,
  "time_before_execution": 0.000249641,
  "time_to_execute": 9.4548e-05
}
}
}}
/>