Examples | Character-to-DATE Conversion | Teradata Vantage - 17.10 - Example: IntegerDate Dateform Mode - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)

For example, suppose the session dateform is INTEGERDATE, and the default DATE format of the system is set to 'yyyymmdd' through the tdlocaledef utility.

Consider the following table, where the start_date column uses the default DATE format and the end_date column uses the format 'YYYY/MM/DD':

CREATE TABLE date_log
   (id INTEGER
   ,start_date DATE
   ,end_date DATE FORMAT 'YYYY/MM/DD');

The following INSERT statement works because the character strings match the formats of the corresponding DATE columns and SQL Engine can successfully perform implicit character-to-DATE conversion:

INSERT INTO date_log (1099, '20030122', '2003/01/23');

To perform character-to-DATE conversion on character strings that do not match the formats of the corresponding DATE columns, you must use a FORMAT phrase:

INSERT INTO date_log
   (1047
   ,CAST ('Jan 12, 2003' AS DATE FORMAT 'MMMBDD,BYYYY')
   ,CAST ('Jan 13, 2003' AS DATE FORMAT 'MMMBDD,BYYYY'));