15.00 - DATE-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)

DATE-to-DATE Conversion

Use DATE-to-DATE conversion to convert the format or title of a DATE type.

CAST Syntax

where:

 

Syntax element …

Specifies …

date_expression

a date expression to be converted.

date_data_attribute

one of the following optional data attributes:

  • FORMAT
  • NAMED
  • TITLE
  • ANSI Compliance

    This is ANSI SQL:2011 compliant.

    CAST permits the use of data attributes, such as the FORMAT phrase that enables alternative output formatting of date data. This is a Teradata extension to CAST.

    A DATE-to-DATE conversion involving a DATE type with a dateform of INTEGERDATE is a Teradata extension to the ANSI SQL:2011 standard. This is a Teradata extension to CAST

    Teradata Conversion Syntax

    where:

     

    Syntax element …

    Specifies …

    date_expression

    a date expression to be converted.

    data_attribute

    one of the following optional data attributes:

  • FORMAT
  • NAMED
  • TITLE
  • ANSI Compliance

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

    Example  

    Consider a table named employee that was created with a session dateform mode of INTEGERDATE where dob is a DATE column with a format of M3BDDBY4. To list employees who were born between January 30, 1938, and March 30, 1943, you could specify the date information as follows:

       SELECT name, dob 
       FROM employee 
       WHERE dob BETWEEN 'Jan 30 1938' AND 'Mar 30 1943' 
       ORDER BY dob;

    The result returns the date of birth information as specified for the Employee table:

       Name                  DOB
       ----------    -----------
       Inglis C      Mar 07 1938
       Peterson J    Mar 27 1942

    To change the date format to an alternate form, change the SELECT to:

       SELECT name, dob (FORMAT 'yy-mm-dd') 
       FROM employee 
       WHERE dob BETWEEN 'Jan 30 1938' AND 'Mar 30 1943'
       ORDER BY dob ;

    The format specification changes the display to the following:

       Name               DOB
       ----------    --------
       Inglis C      38-03-07
       Peterson J    42-03-27

    Related Topics

    For details on data types and data attributes, see SQL Data Types and Literals.