17.10 - Conversions That Include Fractional Seconds - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)

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 always 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');