Date Validation

Analytics
Enthusiast

Date Validation

Hello all ..

In one of our processing, where we read from some tables and inserting into a new table, we have a CHR_CD and a CHR_VAL_CD field, both are defined as CHAR fields. If the CHR_CD field is 'RETIREDT', then we want to validate that the CHR_VAL_CD is a valid date. If not, we want to populate a default value in the field. How do I do that ?? Can it be part of the insert or does it have to be a separate update step ?? We do not want to abend the code in any case.

Thanks in advance ..
1 REPLY
Junior Contributor

Re: Date Validation

Hi John,
CHR_VAL_CD might be an invalid date, even when CHR_CD field is 'RETIREDT'?
Is there just one date format, e.g. yyyymmdd?

You can't use a typecast, because "We do not want to abend the code in any case".
A manual check using CASEs and SUBSTRINGs is possible, but awfull.
I'd suggest an Outer Join to a calendar table, e.g.

select
coalesce(c.calendar_date, date '9999-12-31')
from tab left join sys_calendar.calendar c
on CHR_VAL_CD = trim(calendardate (format 'yyyymmdd'))
and CHR_CD = 'RETIREDT'

Dieter