CODEWITHSUNDEEP
teradata
user1768029
user1768029

Reputation: 425

Case statement to validate date in Teradata

I am a newbie in Teradata. I need to check a date column has valid date in case statement. This column is in "YYYY-MM-DD" format. If this field is invalid need to use '1900-01-01' as default. Can anyone please guide me on this?

Upvotes: 0

Views: 7622

Answers (1)

anwaar_hell
anwaar_hell

Reputation: 776

Here is a way to check for it 

select 
case when CALENDAR_DATE is null then  cast('1900-01-01'  as date format 'YYYY-MM-DD')
            else cast(datee as date format 'YYYY-MM-DD')  end valid_date
from YOUR_TABLE  XYZ
LEFT OUTER JOIN 
SYS_CALENDAR.CALENDAR CAL ON (CALENDAR_DATE (FORMAT 'YYYY-MM-DD') (CHAR(10))) = XYZ.datee;

And this a link where all possible solutions had been disscussed previously. https://forums.teradata.com/forum/database/to-find-invalid-dates

Upvotes: 1

Related Questions