Midrange News for the IBM i Community

Posted by: renojim
sql date failure
has no ratings.
Published: 08 Dec 2011
Revised: 23 Jan 2013 - 4138 days ago
Last viewed on: 21 May 2024 (5617 views) 

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.

sql date failure Published by: renojim on 08 Dec 2011 view comments(3)

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.

Return to midrangenews.com home page.
Sort Ascend | Descend


(Sign in to Post a Comment)
Posted by: renojim
Premium member *
Comment on: sql date failure
Posted: 12 years 5 months 15 days 7 hours 32 minutes ago

Fixed it with

 exec sql set option datfmt = *ISO;

though I have no idea why.

Posted by: Smith5646
Premium member *
Columbus, Ohio
Comment on: sql date failure
Posted: 12 years 5 months 15 days 5 hours 20 minutes ago

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.

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: sql date failure
Posted: 12 years 5 months 14 days 18 hours 8 minutes ago

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.