15.00 - Numeric-to-DATE Conversion - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

Numeric-to-DATE Conversion

Purpose

Converts a numeric expression to a DATE data type.

CAST Syntax

where:

 

Syntax element …

Specifies …

numeric_expression

an expression or existing field having a numeric data type.

data_attribute

any of the following optional data attributes:

  • FORMAT
  • NAMED
  • TITLE
  • A date_data_definition that specifies a FORMAT clause enables an alternative format.

    Specifying data attributes in CAST is a non-ANSI Teradata extension.

    ANSI Compliance

    This is a Teradata extension to the ANSI SQL:2011 standard.

    Teradata Conversion Syntax

    where:

     

    Syntax element …

    Specifies …

    numeric_expression

    an expression or existing field having a numeric data type.

    data_attribute

    one of the following optional data attributes:

  • FORMAT
  • NAMED
  • TITLE
  • Specifying a FORMAT clause enables an alternative format.

    ANSI Compliance

    This is a Teradata extension to the ANSI SQL:2011 standard.

    Translation of Numbers to Dates

    Although not recommended, you can explicitly convert numbers to dates.

    Teradata Database stores each DATE value as a four-byte integer using the following formula:

       (year - 1900) * 10000 + (month * 100) + day

    For example, December 31, 1985 would be stored as the integer 851231; July 4, 1776 stored as -1239296; and March 30, 2041 stored as 1410330.

    The following table demonstrates how numeric dates are interpreted when inserted into a column. Note the translation of the third date, which was probably intended to be 1990‑12‑01.

     

    This numeric value …

    Translates to this date value …

    901201

    1990-12-01

    1001201

    2000-12-01

    19901201

    3890-12-01

    Notice that this formula best fits two-digit dates in the 1900s. Because of the difficulty of using this format outside of the 1900s, dates are best specified as ANSI date literals instead.

    Range of Allowable Values

    Allowable date values range from AD January 1, 0001 (-18989899) to AD December 31, 9999 (80991231).

    If the numeric value does not represent a valid date, an error is reported.

    Numeric-to-DATE Implicit Type Conversion

    Although not recommended, you can specify a numeric type in the assignment of a DATE type. Teradata Database performs implicit numeric-to-DATE type conversion prior to the assignment. The value of the numeric type must represent a valid date.

    However, for comparison operations involving a numeric type operand and a DATE type operand, Teradata Database converts the DATE type to a numeric type. If you compare a numeric type and a DATE type and expect the comparison to be between two DATE types, you must explicitly convert the numeric type to a DATE type.

    Example  

    This example casts the numeric integer expression to a date format.

       SELECT CAST (1071201 AS DATE);

    The result looks like this when the DateForm mode of the session is set to ANSIDate:

          1071201
       ----------
       2007-12-01

    Related Topics

     

    FOR information on …

    SEE …

    implicit type conversion of operands for comparison operations

    “Implicit Type Conversion of Comparison Operands” on page 500.

    data type compatibility rules for assignments involving DateTime types

    “ANSI DateTime and Interval Data Type Assignment Rules” on page 822.

    data type compatibility rules for arithmetic operations involving DateTime types

    “Arithmetic Operators and ANSI DateTime and Interval Data Types” on page 842.

    data types and data attributes

    SQL Data Types and Literals.