How to find and fix Invalid Date Error in Teradata

Invalid Date error in Teradata is one of the most common date related error we face while loading data from Source to Target. The error is because either the source is not sending date values in format which is same as Target or the date value is not correct. Eg: If Target is expecting date in format 'YYYY-MM-DD' then '2015-02-31' is date in format 'YYYY-MM-DD' however the value is not correct as February cannot have 31 as date. Other example could be '2015/02/25' is date with not same format as Target however value is correct. Both the error are pretty common. Now let us see how to identify date values which may be correct format wise however are incorrect in value.

CREATE MULTISET VOLATILE TABLE vt_date
(
out_date VARCHAR(10)
)
PRIMARY INDEX(out_date)
ON COMMIT PRESERVE ROWS;

INSERT INTO vt_date SEL '2015-05-17'; /* valid date */
INSERT INTO vt_date SEL '2015-17-05'; /* invalid date - month cannot be more than 12 */
INSERT INTO vt_date SEL '2015-01-32'; /* invalid date - date cannot be more than 31 */

We will use sys_calendar.calendar to identify the wrong date values. If you data set that ranges for date not present in "calendar" view then you can create a custom table and use that. But chances of this happening is very less.

SEL tb1.out_date
FROM
vt_date tb1
LEFT OUTER JOIN sys_calendar.calendar tb2 ON
tb1.out_date = CAST(CAST(tb2.calendar_date AS DATE FORMAT 'y4-mm-dd') AS VARCHAR(10))
WHERE
tb2.calendar_date IS NULL;
--Output
--2015-17-05
--2015-01-32

Now we have identified invalid dates, it is time to fix it. There are different ways by which you can fix this issue. Either you can remove such entries or you can fix it. And easiest method to fix it is to assign some default value to erroneous values.

UPDATE
	vt_date
SET
	out_date = '1900-01-01' /* assign default values */
WHERE
	out_date IN (SEL tb1.out_date
FROM
	vt_date tb1
LEFT OUTER JOIN sys_calendar.calendar tb2 ON
	tb1.out_date = CAST(CAST(tb2.calendar_date AS DATE FORMAT 'y4-mm-dd') AS VARCHAR(10))
WHERE
	tb2.calendar_date IS NULL);

Hope this helps.