15.10 - Timestamp Literals - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

Declares a timestamp value in an expression.

where string specifies a character string in apostrophes in one of four formats.

 

This format...

Represents...

YYYY-MM-DD hh:mi:ss

timestamp with no time zone or fractional seconds digits:

  • YYYY represents year. The valid range is 0001 through 9999, inclusive. You must specify all four digits.
  • MM represents month. The valid range is 01 through 12, inclusive. You must specify both digits.
  • DD represents day. The valid range is 01 through 31, inclusive, constrained by Gregorian calendar definitions. You must specify both digits, and follow them by a single pad character.
  • hh represents the hour of the day. The valid range is 00–23, inclusive. You must specify both digits.
  • mi represents minute of the hour. The valid range is 00–59, inclusive. You must specify both digits.
  • ss represents seconds. The valid range is 00–61. You must specify both digits.
  • YYYY-MM-DD hh:mi:sssignhh:mi

    a timestamp with a specified time zone offset, but no fractional seconds digits.

  • YYYY represents year. The valid range is 0001 through 9999, inclusive. You must specify all four digits.
  • MM represents month. The valid range is 01 through 12, inclusive. You must specify both digits.
  • DD represents day. The valid range is 01 through 31, inclusive, constrained by Gregorian calendar definitions. You must specify both digits, and follow them by a single pad character.
  • hh represents the hour of the day. The valid range is 00–23, inclusive. You must specify both digits.
  • mi represents minute of the hour. The valid range is 00–59, inclusive. You must specify both digits.
  •  

    This format...

    Represents...

     

  • ss represents seconds. The valid range is 00–61. You must specify both digits.
  • signhh:mi represents the hours and minutes in the Time Zone offset. The valid range is -12:59 through +13:00, inclusive.
  • sign is + or -

    YYYY-MM-DD hh:mi:ss.ssssss

    a timestamp with up to six fractional seconds digits, but no Time Zone:

  • YYYY represents year. The valid range is 0001 through 9999, inclusive. You must specify all four digits.
  • MM represents month. The valid range is 01 through 12, inclusive. You must specify both digits.
  • DD represents day. The valid range is 01 through 31, inclusive, constrained by Gregorian calendar definitions. You must specify both digits, and follow them by a single pad character.
  • hh represents the hour of the day. The valid range is 00–23, inclusive. You must specify both digits.
  • mi represents minute of the hour. The valid range is 00–59, inclusive. You must specify both digits.
  • ss.ssssss represents seconds. The valid range for the first two digits is 00–61. You must specify both digits. You can specify from one to six fractional digits.
  • YYYY-MM-DD hh:mi:ss.sssssssignhh:mi

    a timestamp with up to six fractional seconds and a time zone offset:

  • YYYY represents year. The valid range is 0001 through 9999, inclusive. You must specify all four digits.
  • MM represents month. The valid range is 01 through 12, inclusive. You must specify both digits.
  • DD represents day. The valid range is 01 through 31, inclusive, constrained by Gregorian calendar definitions. You must specify both digits, and follow them by a single pad character.
  • hh represents the hour of the day. The valid range is 00–23, inclusive. You must specify both digits.
  • mi represents minute of the hour. The valid range is 00–59, inclusive. You must specify both digits.
  • ss.ssssss represents seconds. The valid range for the first two digits is 00–61. You must specify both digits. You can specify from one to six fractional digits.
  • signhh:mi represents the hours and minutes in the Time Zone offset. The valid range is -12:59 through +13:00, inclusive.
  • sign is + or -

    The colons are required between the hour, minute, and second elements, and the decimal point is required if fractional seconds are specified. A decimal point is not allowed if there are no fractional digits. Spaces and new line characters are not allowed in a literal except after the keyword TIMESTAMP.

     

    IF the format of the Timestamp literal is …

    THEN the data type is …

    YYYY-MM-DD hh:mi:ss

    TIMESTAMP(0).

    YYYY-MM-DD hh:mi:sssignhh:mi

    TIMESTAMP(0) WITH TIME ZONE.

    YYYY-MM-DD hh:mi:ss.ssssss

    TIMESTAMP(n), where n is the number of fractional seconds digits.

    YYYY-MM-DD hh:mi:ss.sssssssignhh:mi

    TIMESTAMP(n) WITH TIME ZONE, where n is the number of fractional seconds digits.

    The following example selects all classes from the Classes table that are timestamped November 23 2006 at 3:30:23 PM.

    SELECT *
    FROM Classes
    WHERE Time_stamp = TIMESTAMP '2006-11-23 15:30:23';
    SELECT *
    FROM Classes
    WHERE Time_stamp = TIMESTAMP '2002-01-01 10:37:12-08:00'
    SELECT *
    FROM Classes
    WHERE Time_stamp = TIMESTAMP '1995-07-31 10:36:02.123456'
    SELECT *
    FROM Classes
    WHERE Time_stamp = TIMESTAMP '1492-10-27 21:17:35.456123+07:30'
     

    FOR information on …

    SEE …

    TIMESTAMP data types

    “TIMESTAMP Data Type” on page 142.

    TIMESTAMP WITH TIME ZONE data types

    “TIMESTAMP WITH TIME ZONE Data Type” on page 148.