Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.
I have an sql table with four date fields. I set them to 0001-01-01. I run Pgm A that puts actual dates in some of the fields. Pgm B reads the table looking for dates that have been set to something other than 0001-01-01 by pgm A. I've run this thing 100's of times in dev w/o a problem. Put it in production, ran fine the first time. Second time, on FETCHING the first record, I get CPF5035-18 'There is data in a date, time, or timestamp field that is not valid'. It's the first date field in the first record, still set to 0001-01-01. I have datfmt (*ISO) on the pgm compile.
I don't see anything wrong with the data, it's as it has always been for hundreds of test iterations and 1 in prd. I'm stumped.
Fixed it with
exec sql set option datfmt = *ISO;
though I have no idea why.
datfmt(*iso) sets the format for the RPG only. It does not have an effect on the SQL. I don't have access to a system right now so I can't look to see where the SQL default is set. Maybe someone else can post that infor or maybe I'll remember to look tomorrow.
SQL date intermediate field default is a 2 digit year. This means that anything outside of 01/01/40 (1940) thru 12/31/39 (2039) is read as invalid - the internal SQL field cannot hold the value. As noted if you begin your program with SET OPTION DATFMT = *ISO, then the internal SQL date fields will default to a full 4-digit year size, so any date from 01/01/0001 thru 12/31/9999 will be valid.
Not sure why you didn't set the date fields as null capable - saves having a meaningless value in a field for "I don't know" - that's why null was invented.