How to find and fix Invalid Date Error in Teradata

Invalid Date 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 */

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;

Since you 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 errorneous 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.

Leave a Reply

Your email address will not be published. Required fields are marked *