etto-timestamp-and-to-date-notes.md

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