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.
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.
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.
Hope this helps.