etto-timestamp-and-to-date-notes.md
-
TO_TIMESTAMP
,TO_TIMESTAMP_TZ
, andTO_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, becauseTO_TIMESTAMP
expects one space only.
-
-
The
YYYY
conversion from string toTIMESTAMP
orDATE
has a restriction if you use a year with more than four digits. You must use a non-digit character or template afterYYYY
, 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
orDATE
, 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.