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

# Date and Time Formatting Functions

The following table describes the functions that convert strings to specific data types and vice versa. Those functions are used when the required input/output format of a specific `DATE/TIMESTAMP` is different from the default `DATE/TIMESTAMP` formats.

The default `DATE` format is: `YYYY-MM-DD`

The default `TIMESTAMP` format is: `YYYY-MM-DD HH24:MI:SS.MS`

In all the functions below, the first argument is the value to be converted and the second argument is a template that defines the output or input format. The input string is of one of the [Character types](/sql/data-types/character-types).

| Function                                                    | Description and Comments                                                                                         | Example                                                                                                                                                                    |
| :---------------------------------------------------------- | :--------------------------------------------------------------------------------------------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `TO_DATE(STRING, format)`                                   | Converts string to `DATE` according to the given format<br />Returns `DATE`                                      | `to_date('21 Jun 2023', 'dd mon YYYY')`<br />=> 2023-06-21                                                                                                                 |
| `TO_TIMESTAMP(STRING, format)`                              | Converts string to `TIMESTAMP` according to the given format<br />Returns `TIMESTAMP`                            | `TO_TIMESTAMP('_2023-12-13_ 04:05:06.789', 'yyyy-dd-mm hh24:mi:ss.ms')`<br />=> 2023-12-13 04:05:06.789                                                                    |
| `TO_CHAR(DATE, format)` <br /> `TO_CHAR(TIMESTAMP, format)` | Converts a `DATE` or `TIMESTAMP` data type to a string in the given format<br />Returns `VARCHAR`<br />See notes | `TO_CHAR(date '2022-12-12', 'yy-mm-dd')`<br />=> 22-12-12<br /><br />`TO_CHAR(TIMESTAMP '2022-12-24 16:27:30', 'dd-mm-yyyy HH12:MI:SS am')`<br />=> 24-12-2022 04:27:30 pm |

Template Patterns for Date/Time Formatting:

| Pattern                | Description and Comments                                   |
| :--------------------- | :--------------------------------------------------------- |
| `HH`                   | hour of day (01-12)                                        |
| `HH12`                 | hour of day (01-12)                                        |
| `HH24`                 | hour of day (00-23)                                        |
| `MI`                   | minute (00-59)                                             |
| `SS`                   | second (00-59)                                             |
| `MS`                   | millisecond (000-999)                                      |
| `US`                   | microsecond (000000-999999)                                |
| `YYYY`                 | year (4 digits)                                            |
| `YY`                   | last 2 digits of year                                      |
| `MM`                   | month number (01-12)                                       |
| `Mon`                  | First three letters of the month ('Jan', 'Feb', and so on) |
| `DD`                   | day of month (01-31)                                       |
| `AM`, `am`, `PM`, `pm` | meridiem indicator                                         |

## Examples

Convert the input format to a DATE/TIMESTAMP that can identified as such:

```sql theme={null}
SELECT TO_DATE('2012-JAN-12','YYYY-MON-DD');
SELECT TO_DATE('20/12/12','YY/MM/DD');
SELECT TO_DATE('2012.07.23', 'YYYY.MM.DD');
SELECT TO_DATE('20200526','YYYYMMDD');
SELECT TO_TIMESTAMP('20/12/12 17:12:12.45','YY/MM/DD HH24:MI:SS.MS');
SELECT TO_TIMESTAMP('2010-Aug-09 12:00:01 PM' , 'yyyy-Mon-dd HH:MI:SS PM');
```

Convert the DATE/TIMESTAMP to the desired output format:

```sql theme={null}
SELECT TO_CHAR(TIMESTAMP '2022-12-12 16:27:30.41', 'dd-mm-yyyy HH12:MI:SS
am');
SELECT TO_CHAR(date_column, 'YY-MM-DD HH:MI:SS.MS AM') FROM my_table;
```