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 Teradata Database 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'));