Converts a string value to a DATE type.

Behavior Type



TO_DATE ( expression , pattern )


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

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.


  • 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');
    (1 row)

    Instead, use a non-digit separator after the year. For example:

    => SELECT TO_DATE('20000-1131', 'YYYY-MMDD');
    (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.


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