Date validation in Teradata

Database
N/A

Date validation in Teradata

Hi all,
I have a character field that has date values in the form yyyymmdd. Some of these are valid dates and some are not. Can someone suggest a way
to insert the valid dates into a date column and set the non valid dates to null. I tried to substring the character date and check that the year is between x and y and the month is between 1 and 12 and the day is between 1 and 31. This almost works except for months that don't have 31 days etc... Is there any function to check the valid dates in teradata like IS_DATE in oracle.Any suggestions?
2 REPLIES
Enthusiast

Re: Date validation in Teradata

I cannot recall the names of the wise guy who suggested this idea, but with due credit to that genius, this is the technique that you can use.

SELECT T.* FROM
MYTABLE T
INNER JOIN
SYS_CALENDAR.CALENDAR C
ON T.C1 = C.CALENDAR_DATE (FORMAT 'YYYYMMDD' ) (CHAR(8))

that way you will get all the records for only valid date values.

use the same logic to build any update queries that you would need. (you can use left outer join to update those records with bad date as well )
Enthusiast

Re: Date validation in Teradata

If you're on a version of Teradata that supports UDF's (v2r6 or v2r6.1, I believe), then an example "IS_DATE" UDF is available for download at the teradata web site along with many other Oracle functions. You would have to change it to return NULL in the case of a format error instead of abending.