CASE STATEMENT TO VALIDATE VALID DATE DATATYPE FOR COLUMN ?

General
Enthusiast

CASE STATEMENT TO VALIDATE VALID DATE DATATYPE FOR COLUMN ?

Hello,

I need to write a case statement for checking valid date data type for column in my table.

table is having BUS_DT date format 'yy/mm/dd' as column.

How can I check in case statement if date value in table is valid date then return BUS_DT value else ''.

Please advice me.

Regards,

Mayank

3 REPLIES
Enthusiast

Re: CASE STATEMENT TO VALIDATE VALID DATE DATATYPE FOR COLUMN ?

Which TD version are you on?

You can use the UDF ISDATE in the CASE statement to check whether the string provided is a valid date or not agsinst 20 different date formats...

http://www.teradataforum.com/isdate.htm

Enthusiast

Re: CASE STATEMENT TO VALIDATE VALID DATE DATATYPE FOR COLUMN ?

The first thing is if the column definition is BUS_DT date format 'yy/mm/dd' then it will not allow invalid date during insertion. 

Invalid values  are possible in case the column is defined as Character.

Can you please tell your concern about invalid date in this column? Or some sample data for this column?

Thanks,

Khurram
Enthusiast

Re: CASE STATEMENT TO VALIDATE VALID DATE DATATYPE FOR COLUMN ?

You could try LEFT JOIN with Sys_Calendar tables, and using COALESCE in the SEL part