All Forums Analytics
Renji 17 posts Joined 08/05
17 Nov 2007
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 ..

Regards John Abraham
dnoeth 4628 posts Joined 11/04
17 Nov 2007

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 con CHR_VAL_CD = trim(calendardate (format 'yyyymmdd'))and CHR_CD = 'RETIREDT'Dieter

Dieter

You must sign in to leave a comment.