Forcing a FORMAT on CAST for Converting Character to DATE | Teradata Vantage - Forcing a FORMAT on CAST for Converting Character to DATE - Teradata Vantage - Analytics Database

You can use a FORMAT phrase to convert a character string that does not match the format of the target DATE data type. A character string in a conversion that does not specify a FORMAT phrase uses the output format for the DATE data type.

For example, suppose the session dateform is INTEGERDATE and the default DATE format of the system is set to 'yyyymmdd' through the tdlocaledef utility. The following statement fails, because the character string contains separators, which does not match the default DATE format:

SELECT CAST ('2005-01-01' AS DATE);

To override the default DATE format, and convert a character string that contains separators, specify a FORMAT phrase for the DATE target type:

SELECT CAST ('2005-01-01' AS DATE FORMAT 'YYYY-MM-DD');

In character-to-DATE conversions, the FORMAT phrase must not consist solely of the following formatting characters.

SELECT CAST ('20050101' AS DATE FORMAT 'YYYYMMDD');

'20050101'
----------
  20050101

SELECT CAST ('050101' AS DATE FORMAT 'YYMMDD');

'050101'
--------
  050101

SELECT CAST ('2005+01+01' AS DATE FORMAT 'YY/MM/DD');

'2005+01+01'
------------
    05/01/01