Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
SQL UDF Function for Dec to Date Conversion
has no ratings.
Published: 26 Feb 2013
Revised: 26 Mar 2015 - 911 days ago
Last viewed on: 22 Sep 2017 (6687 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 UDF Function for Dec to Date Conversion Published by: Bob Cozzi on 26 Feb 2013 view comments(7)

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

COMMENTS

(Sign in to Post a Comment)
Posted by: neilrh
Premium member *
Jackson, MI
Comment on: SQL UDF Function for Dec to Date Conversion
Posted: 4 years 6 months 25 days 22 hours 24 minutes ago

You say you only accept 8 and 6 digit field - doesn't cymd require a 7 digit length?

Your cymd extract does not take into account the "c" part, which under IBM usage standards would start your date at 01-01-1900, and the "c" value should then be added to the 19.

You load the field rtnDate, but then return a different value.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQL UDF Function for Dec to Date Conversion
Posted: 4 years 6 months 25 days 21 hours 32 minutes ago
Edited: Thu, 26 Mar, 2015 at 13:11:37 (911 days ago)

Neil,

Thanks for the bug catch (the return value).

I was a having trouble getting CYMD format to work. It ended up being when the CVTDATE UDF is compiled, I had to specify DATFMT(*ISO) in order for dates prior to 1940 to be considered valid. Initially I had changed my SQL session from DATFMT(*JOB) to DATFMT(*USA) but I also had to do that in the CVTDATE routine.

The revised code is now posted, above.

Thanks.

 

Posted by: Ringer
Premium member *
Comment on: SQL UDF Function for Dec to Date Conversion
Posted: 4 years 6 months 25 days 19 hours 44 minutes ago
Edited: Wed, 27 Feb, 2013 at 10:50:31 (1668 days ago)

Parms look good. I always use DECIMAL and VARCHAR because SQL will cast integers (like literals) to DECIMAL and CHAR to VARCHAR.

I think you need an error handler in there in case the date() blows up on an invalid date. It's like a *PSSR or Monitor/On-Error or MONMSG.

-- Error handler                     
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN                                
  SET rtnDate = date('0001-01-01');  
END ;                                

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmst.pdf

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0009027.htm

Chris Ringer

 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQL UDF Function for Dec to Date Conversion
Posted: 4 years 6 months 25 days 18 hours 19 minutes ago
Edited: Wed, 27 Feb, 2013 at 12:37:23 (1668 days ago)

Thanks Chris... modified the code above to include the error trap and removed the logic that tested the date components.

Posted by: Ringer
Premium member *
Comment on: SQL UDF Function for Dec to Date Conversion
Posted: 4 years 6 months 25 days 17 hours 25 minutes ago

You owe me big time Bob! Not.

Glad I could help some.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQL UDF Function for Dec to Date Conversion
Posted: 4 years 6 months 24 days 16 hours 29 minutes ago

Still not good yet. If the RTNDATE ends up getting an invalid date inserted into it, it fails. Can't seem to trap that error ("Date mapping error") Hmmm....

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQL UDF Function for Dec to Date Conversion
Posted: 4 years 6 months 24 days 13 hours 22 minutes ago

Yep, had to change the handler and also do some date testing. (See above modified code).

Now when I validate the date, I use a year range, and month range, and then using those two values, build a YYYY-MM-01 date (first of the month) and then do the math to get an end of month date, extract its day, and then test the input day against it. If it's <= then I attempt the conversion.

If the conversion then fails anyway, the handler kicks in and returns null. Seems to work much cleaner now, but who knows, maybe more to come.