TO_DATE

This topic shared in two places: Date/Time functions and Formatting Functions.

Converts a string value to a DATE type.

Behavior type

Syntax

TO_DATE ( expression , pattern )

Parameters

expression

Specifies the string value to convert, either CHAR or VARCHAR.

pattern

A CHAR or VARCHAR that specifies an output pattern string. See:

Input value considerations

TO_DATE requires a CHAR or VARCHAR expression. For other input types, use TO_CHAR to perform an explicit cast to a CHAR or VARCHAR before using this function.

Notes

To use a double quote character in the output, precede it with a double backslash. This is necessary because the backslash already has a special meaning in a string constant. For example: '\\"YYYY Month\\"'

TO_TIMESTAMP, TO_TIMESTAMP_TZ, and TO_DATE skip multiple blank spaces in the input string if the FX option is not used. FX must be specified as the first item in the template. For example:
- TO_TIMESTAMP('2000 JUN', 'YYYY MON') is correct.
- TO_TIMESTAMP('2000 JUN', 'FXYYYY MON') returns an error, because TO_TIMESTAMP expects one space only.
The YYYY conversion from string to TIMESTAMP or DATE has a restriction if you use a year with more than four digits. You must use a non-digit character or template after YYYY, otherwise the year is always interpreted as four digits. For example, given the following arguments, TO_DATE interprets the five-digit year 20000 as a four-digit year:
```
=> SELECT TO_DATE('200001131','YYYYMMDD');
  TO_DATE
------------
 2000-01-13
(1 row)
```
Instead, use a non-digit separator after the year. For example:
```
=> SELECT TO_DATE('20000-1131', 'YYYY-MMDD');
   TO_DATE
-------------
 20000-12-01
(1 row)
```
In conversions from string to TIMESTAMP or DATE, the CC field is ignored if there is a YYY, YYYY or Y,YYY field. If CC is used with YY or Y, then the year is computed as (CC–1)*100+YY.

Examples

=> SELECT TO_DATE('13 Feb 2000', 'DD Mon YYYY');
  to_date
------------
 2000-02-13
(1 row)