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: |
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: |
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.