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

<AgentInstructions>

## Submitting Feedback

If you encounter incorrect, outdated, or confusing documentation on this page, submit feedback:

POST https://docs.firebolt.io/feedback

```json
{
  "path": "/reference-sql/functions-reference/date-and-time/to-date",
  "feedback": "Description of the issue"
}
```

Only submit feedback when you have something specific and actionable to report.

</AgentInstructions>

> Reference material for TO_DATE function

# TO_DATE

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 string to `DATE` type using format.

## Syntax

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

## Parameters

| Parameter      | Description                                                                                                                                                                                                                                                                                                                                                                              | Supported input types |
| :------------- | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :-------------------- |
| `<expression>` | The text to convert to a date. If no optional `<format>` argument is given that can be used to parse the `<expression>`, the following formats are supported: `'YYYY-M[M]-D[D]'` (e.g., `2023-3-28`), `'YYYY-month-D[D]'` (e.g., `2023-FEB-12`), `'month-D[D]-YYYY'` (e.g., `Dec-01-2023`), `'D[D]-month-YYYY'` (e.g., `3-jun-2023`), and `'month D[D], YYYY'` (e.g., `august 12, 2023`) | `TEXT`                |
| `<format>`     | Optional. A string literal that specifies the format of the `<expression>` to convert.                                                                                                                                                                                                                                                                                                   | `TEXT` (see below)    |

Accepted `<format>` patterns include the following specifications:

| Format option | Description                                        | Example                                        |
| :------------ | :------------------------------------------------- | :--------------------------------------------- |
| `YYYY`        | Year (4 or more digits)                            | `TO_DATE('2023', 'YYYY'); --> '2023-01-01'`    |
| `YYY`         | Last 3 digits of year                              | `TO_DATE('2023', 'YYY'); --> '2023-01-01'`     |
| `YY`          | Last 2 digits of year                              | `TO_DATE('2023', 'YY'); --> '2023-01-01'`      |
| `Y`           | Last digit of year                                 | `TO_DATE('2023', 'Y'); --> '2023-01-01'`       |
| `MONTH`       | Full month name (case insensitive)                 | `TO_DATE('august', 'MONTH'); --> '0001-08-01'` |
| `MON`         | abbreviated month name (3 chars, case insensitive) | `TO_DATE('dec', 'MON'); --> '0001-12-01'`      |
| `MM`          | Month number (01–12)                               | `TO_DATE('7', 'MM'); --> '0001-07-01'`         |
| `DD`          | Day of month (01–31)                               | `TO_DATE('15', 'DD'); --> '0001-01-15'`        |

**Usage notes for formatting**

* Case letters in the input `<expression>` are ignored
* A separator (non-digit and non-letter) in the `<format>` string will match exactly one separator or is skipped
* Any non-separator in the `<format>` that is not part of a format option will match exactly one other character.
* Any character in quotes `"` will match exactly one other character.
* If the year format specification is `'YYY'`, `'YY'`, or `'Y'` and the supplied year is less than four digits, the year will be adjusted to be nearest to the year 2020, (e.g., `80` becomes `1980`).
* More specification, such as `'HH'`, `'MI'`, or, `'TZH'`, are accepted but ignored for purposes of computing the `DATE` result.
* Modifiers (e.g., `'FM'`) are not supported.

Some additional format patterns are reserved but currently not implemented: `FF1`, `FF2`, `FF3`, `FF4`, `FF5`, `FF6`, `SSSS`, `SSSSS`, `IYYY`, `IYY`, `IY`, `I`, `BC`, `AD`, `B_DOT_C_DOT`, `A_DOT_D_DOT`, `DAY`, `DY`, `DDD`, `IDDD`, `D`, `ID`, `W`, `WW`, `IW`, `CC`, `J`, `Q`.
Using them in the format string raises an error.

## Return Type

`DATE`

## Examples

The following example shows that separators and non-separators can cause skips. The separator `' '` (space) in the `<format>` matches the other separator `'/'` in the `<expression>`. The non-separator `'x'` will match any other character, in this case the `'a'`. Lastly, the two separators `'++'` will match up to two other separators, here the first `'x'` matches `'.'` while the second `'x'` will simply be ignored as no other separators follow.

<QueryWindow
  content={{
"sql": "SELECT TO_DATE('2023/aJUN.23', 'YYYY xMON++DD');",
"result": {
"data": [
  [
    "2023-06-23"
  ]
],
"meta": [
  {
    "name": "?column?",
    "type": "date"
  }
],
"query": {
  "query_id": "779c57ec-6ca2-4f09-bab6-8ca3978fce20",
  "query_label": null,
  "request_id": "4d47503a-3295-4004-8c80-62c72adb8467"
},
"rows": 1,
"statistics": {
  "bytes_read": 1,
  "elapsed": 0.006324,
  "rows_read": 1,
  "scanned_bytes_cache": 0,
  "scanned_bytes_storage": 0,
  "time_before_execution": 0.000263178,
  "time_to_execute": 8.8762e-05
}
}
}}
/>

The following example shows how the year is adjusted to be nearest to 2020 because `YYY` was used to match a number that contains less than four digits. To receive the exact year `'180'` use `YYYY` instead.
Furthermore, as the three separators are quotes `"..."` they will match any character (separator or non-separator) which in this case is `'ar '`.

<QueryWindow
  content={{
"sql": "SELECT TO_DATE('Year 180: August 4th', 'xx\"...\"yyy: month DDxx');",
"result": {
"data": [
  [
    "2180-08-04"
  ]
],
"meta": [
  {
    "name": "?column?",
    "type": "date"
  }
],
"query": {
  "query_id": "359d1209-bd2c-45dd-b5d0-66b3d1c1aaab",
  "query_label": null,
  "request_id": "75b0aa16-3e34-43d5-b3fd-8960820c4372"
},
"rows": 1,
"statistics": {
  "bytes_read": 1,
  "elapsed": 0.006965,
  "rows_read": 1,
  "scanned_bytes_cache": 0,
  "scanned_bytes_storage": 0,
  "time_before_execution": 0.000243818,
  "time_to_execute": 8.8126e-05
}
}
}}
/>