DATE-to-Character Conversion
Purpose
Converts a DATE value to a character string.
CAST Syntax
where:
|
Syntax element … |
Specifies … |
|
date_expression |
a date expression to be cast to a character string. |
|
character_data_type |
the character data type to which date_expression is to be converted. |
|
character_data_attribute |
one of the following optional data attributes: |
ANSI Compliance
This is ANSI SQL:2011 compliant.
As an extension to ANSI, CAST permits the use of character data attribute phrases.
Teradata Conversion Syntax
where:
|
Syntax element … |
Specifies … |
|
date_expression |
a date expression to be cast to a character string. |
|
data_attribute |
one of the following optional data attributes: |
|
character_data_type |
the character data type to which date_expression is to be converted. |
|
server_character_set |
the server character set to use for the conversion. If the CHARACTER SET clause is omitted, the user default character set is used for the conversion. |
ANSI Compliance
This is a Teradata extension to the ANSI SQL:2011 standard.
Usage Notes
When converting DATE to CHAR(n) or VARCHAR(n), then n must be equal to or greater than the length of the DATE value as represented by a character string literal.
|
IF the target data type is … |
AND n is … |
THEN … |
|
CHAR(n) |
greater than the length of the DATE value as represented by a character string literal |
trailing pad characters are added to pad the representation. |
|
too small |
a string truncation error is returned. |
|
|
VARCHAR(n) |
greater than the length of the DATE value as represented by a character string literal |
no blank padding is added to the character representation. |
|
too small |
a string truncation error is returned. |
Restrictions
DATE types cannot be implicitly or explicitly converted to character types if the server character set is GRAPHIC.
DATE to CLOB conversion is not supported.
Forcing a FORMAT on CAST for Converting DATE to Character
The default format for DATE to character conversion uses the format in effect for the DATE value.
To override the default format, you can convert a DATE value to a string using a FORMAT phrase. The resulting format, however, is the same as the DATE value. If you want a different format for the string value, you need to also use CAST as described here.
You must use nested CAST operations in order to convert values from DATE to CHAR and force an explicit FORMAT on the result regardless of the format associated with the DATE value. This is because of the rules for matching FORMAT phrases to data types.
Example
The dateform mode of the session is INTEGERDATE and column F1 in the table INTDAT is a DATE value with the explicit format ' YYYY,MMM,DD '.
SELECT F1 FROM INTDAT ;
The result (without a type change) is the following report:
F1
----------
1900,Dec,31
Assume that you want to convert this to a value of CHAR(12), and an explicit output format of ' MMMBDD,BYYYY '. Use nested CAST phrases and a FORMAT to obtain the desired result: a report in character format.
SELECT
CAST( (CAST (F1 AS FORMAT 'MMMBDD,BYYYY')) AS CHAR(12))
FROM INTDAT;
The result after the nested CASTs is the following report.
F1
------------
Dec 31, 1900
The inner CAST establishes the display format for the DATE value and the outer CAST indicates the data type of the desired result.
Example
Suppose you need to create a script to convert date values to the ANSI DATE format, regardless of the source of the DATE value or the DATEFORM mode of the session.
You can use nested CASTs and a FORMAT to do this as demonstrated by the example that follows.
SELECT
CAST( (CAST (F1 AS FORMAT 'YYYY-MM-DD')) AS CHAR(10))
FROM INTDAT;
The result after the nested CASTs is the following report.
F1
----------
1900-12-31
Related Topics
For details on data types and data attributes, see SQL Data Types and Literals.