There are two steps.
Step 1: Finding existing bad data.
Step 2: Preventing further bad data.
Step 1: Finding existing bad data.
Pretend your FAKEDATE field is 8 characters. And it should be stored as
MMDDYYYY.
And there is no 32 day of December.
INSERT INTO ROB/FUNDOT (FAKEDATE) VALUES('12312014')
Then read about idate at
http://www.think400.dk/downloads.htm
and try
select fakedate,
iDATE(FAKEDATE,'*MDCCYY')
from rob/fundot
order by 2 desc
....+....1....+...
FAKEDATE IDATE
12322014 -
12312014 12/31/14
******** End of data
Step 2: Preventing further bad data.
Ideally, if you switched to 'real' date fields you would no longer have
bad dates.
However, if you decide that the cost of switching to real dates is very
high and are interested in an alternative then you may wish to see if the
prevention can be done via a 'check' constraint. Pretend your FAKEDATE
field is 8 characters. And it should be stored as MMDDYYYY. Then try
this:
ADDPFCST FILE(ROB/FUNDOT) TYPE(*CHKCST) CHKCST('date(substr(fakedate,5,4)
concat ''-'' concat substr(fakedate,1,2) concat ''-'' concat
substr(fakedate,3,2)) between date(''2000-01-01'') and
date(''2099-12-31'')')
A good date still goes in fine:
INSERT INTO ROB/FUNDOT (FAKEDATE) VALUES('12312014')
1 rows inserted in FUNDOT in ROB.
Then if you try to add a bad date:
INSERT INTO ROB/FUNDOT (FAKEDATE) VALUES('12322014')
INSERT, UPDATE, or MERGE not allowed by CHECK constraint.
Of course, if you add a bad date in a RPG program it will error out also.
Now, if the logic in your RPG program assumes that any row addition error
is only possible because the row already exists and then you must try an
update instead of a write....
Don't laugh, I've seen this crud.
See also:
RMVPFCST FILE(ROB/FUNDOT) CST(*all) TYPE(*CHKCST)
http://www.think400.dk/files/idate_readme.rtf
Rob Berendt
midrange.com
