Character-to-TIMESTAMP Conversions with Fractional Seconds - Teradata VantageCloud Lake

The following rules apply to conversions that include fractional seconds:

• The fractional seconds precision in the source character string must be less than or equal to the fractional seconds precision specified by the target type.

  SELECT CAST('2002-01-01 12:30:25.44' AS TIMESTAMP(3));

  If no fractional seconds appear in the source character string, then the fractional seconds precision is less than or equal to the target data type fractional seconds precision, because the valid range for the precision is zero to six, where the default is six.

  SELECT CAST('2002-01-01 12:30:25' AS TIMESTAMP(3));

• If the target data type is defined by a FORMAT phrase, the fractional seconds precision formatting characters must be greater than or equal to the precision specified by the data type.

  SELECT CAST('12-02-07 12:30:25' AS TIMESTAMP(3) 
  FORMAT 'DD-MM-YYBHH:MI:SSDS(3)');

  A FORMAT phrase must specify a fractional seconds precision of six if the target data type does not specify a fractional seconds precision, because the default precision is six.

  SELECT CAST('12-02-07 12h:15.12s:30m' 
  AS TIMESTAMP FORMAT 'DD-MM-YYBHHh:SSDS(6)s:MIm');