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 - 734 days ago
Last viewed on: 29 Mar 2017 (5908 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)

NOTE: I've updated the embedded code with the final version that I'm going to use in my RPG Report article for March 2013. The final code is here for you to pull down and try out if you like. Once the article is published, this post will be removed. Thanks!

[Original Msg]

I finally broke down and wrote an SQL function (User Defined Function or "UDF") that accepts a numeric value and converts it to a true date data-type. This allows me to select and sort legacy files by the embedded MDY or YMD "date" values.

I created two functions, cvtDate (numeric to date conversion) and charDate (character to date conversion). They are illustrated below:

SELECT ordnbr, qtysld, cvtdate( orddat, 'mdy' )
 FROM orders
  WHERE  cvtDate( orddat, 'mdy') between '2013-01-01' and '2013-02-28' 
  ORDER BY cvtDate( orddate, 'mdy')

The first parameter must be a 6, 7 or 8 position numeric value or a character string of all digits that contains a legacy date in any of the following formats:

  • ymd
  • mdy
  • cymd
  • yyyymmdd
  • cyyyymmddhhmmss

If no date format (2nd parameter) is specified, it defaults to 'ymd'.

Since this is my first UDF, I'd appreciate any suggestions for how to make it better.

NOTE: In Interactive SQL or your own programs, date values are normally returned in the setting assigned to the DATFMT attribute. By default this is set to *JOB, which is normally *MDY. So any date outside of the 1940 to 2039 range shows up as ++++++. Change this setting to DATFMT=*ISO to view the full date. In interactive SQL use Shift+F1 (F13) to change the session default settings.

 -- Convert To Date Format SQL UDF v1.0 (c) Copyright 2013 by R. Cozzi, Jr.
 -- Part of COZTOOLS (Cozzi Tools) Software Package COPR 2013 by R. Cozzi, Jr.

 -- To create the CVTDATE function in a library do the following:
 --    CHGCURLIB  mylib  /* The library where the function should be created */
 --    RUNSQLSTM  COZTOOLS/QSQLSRC MBR(CVTCASE)



 --  SQL Function Syntax:
 --               CVTDATE( non-date [ , 'date-fmt' ] )


 --      Returns   DATE value or NULL if invalid date detected
 --      Parameters:
 --        non-date: Specify the numeric or character database field that
 --                  contains the data you want to convert to a DATE.
 --                  A numeric value up to 8-digits in length may be
 --                  specified, or a character field may be specified.
 --               In the case of a character value, the contents must
 --                  be something that can be translated to a date value;
 --                  normally it only digits and some blanks, but
 --                  date edit-codes are allowed.
 --                  Any non-numeric characters are removed before attempting
 --                  to convert the value to a date.
 --        date-fmt: The format of the data from parameter 1.
 --                  default: 'ymd'
 --                  Specify the date format (enclosed in quotes) for the
 --                  data being converted. This format code is used to
 --                  identify the format of the non-date value so that
 --                  it may be converted to a real DATE value.
 --                  If unspecified, the default format is YYMMDD ('ymd')
 --                  The date formats include:
 --                    ymd, mdy, dmy, cymd, *cl, *outfile, cymdhms
 --                  These values must be enclosed in quotes and specified
 --                  in all lower case. The '*outfile' format code is
 --                  intended for use with character fields from outfile
 --                  created by IBM i CL commands, such as DSPOBJD.
 --                  The 'cymd' and '*cl' formats are synonyms.
 --      Examples:
 --        Convert a 6-digit zoned numeric field in YMD format to date:
 --                  select ordnbr, cvtdate( orddte ) from orders
 --                    ORDER BY cvtdate( orddte ) DESC

 --        Convert a 7-digit packed field in CYMD format to date:
 --                  select item, qtysld, cvtdate( slsdte, 'cymd')
 --                    FROM saleshist
 --                    ORDER BY cvtdate( slsdte, 'cymd') DESC

 --        Convert a 6-position character field in MDY format to date:
 --                  select psnbr, vendor, cvtdate( psdate, 'mdy')
 --                    FROM PACKSLIP


 -- Tips: If running more than once, you may want to unComment the
 --       following 4 DROP statements.
    -- DROP SPECIFIC FUNCTION cvtDate_dectodate_fmt;
    -- DROP SPECIFIC FUNCTION cvtDate_dectodate;
    -- DROP SPECIFIC FUNCTION cvtDate_chartodate_fmt;
    -- DROP SPECIFIC FUNCTION cvtDate_chartodate;

 --       If running on IBM i v7r1 or later, instead of using DROP stmts
 --       consider changing the "CREATE FUNCTION" statements to:
 --                             "CREATE OR REPLACE FUNCTION"
 --       This is a great ease-of-use feature that avoid having the
 --       RUNSQLSTM failing because the DROP statements failed.

 --  For example:
 --  CREATE OR REPLACE FUNCTION cvtDate(inDate dec(8,0), dateFmt varchar(16))


CREATE  FUNCTION cvtDate(inDate dec(8,0), dateFmt varchar(16))
   RETURNS DATE
LANGUAGE SQL
 DETERMINISTIC
   CONTAINS SQL
   SPECIFIC cvtDate_dectodate_fmt
   RETURNS NULL ON NULL INPUT
   NO EXTERNAL ACTION

 SET OPTION COMMIT = *NONE, DATFMT = *ISO,  TIMFMT = *HMS, DBGVIEW = *SOURCE,
                      OUTPUT = *PRINT, TGTRLS = V5R4M0

A:
BEGIN
   DECLARE testDate DATE;
   DECLARE fmt      varchar(16);
   DECLARE theDate  char(10);
   DECLARE theYear  dec(2,0);
   DECLARE maxDay   dec(2,0);
   DECLARE cent     char(2);
   DECLARE yyyy     char(4);
   DECLARE mm       char(2);
   DECLARE dd       char(2);
   DECLARE c        char(1);
   DECLARE cc       dec(2,0);
   DECLARE rtnDate  DATE;

   DECLARE InvalidDate CONDITION FOR '22007';

   DECLARE EXIT HANDLER FOR InvalidDate
     BEGIN
       RETURN CAST(NULL AS DATE);
       SIGNAL SQLSTATE '01HDI' SET MESSAGE_TEXT='Invalid legacy date';
     END;

   SET fmt = trim(dateFmt);
   if (length(fmt) > 1) THEN
      if (substr(fmt,1,1) = '*') THEN
        SET fmt = substr(fmt,2);
      end if;
   end if;

   if (fmt = 'ymd') THEN
          SET theYear = DEC(substr(digits(inDate),3,2),2,0);
          if (theYear >= 40) THEN SET cent = '19';
          ELSE SET cent = '20';
          end if;
          SET yyyy = cent || substr(digits(inDate),3,2);
          SET mm   =  substr(digits(inDate),5,2);
          SET dd   =  substr(digits(inDate),7,2);
   elseif  (fmt = 'mdy') THEN
          SET theYear = DEC(substr(digits(inDate),7,2),2,0);
          if (theYear >= 40) THEN SET cent = '19';
          ELSE SET cent = '20';
          end if;
          set yyyy = cent || substr(digits(inDate),7,2);
          set mm   =  substr(Digits(inDate),3,2);
          set dd   =  substr(Digits(inDate),5,2);
   elseif (fmt = 'iso' or fmt = 'yymd' or fmt = 'yyyymmdd') THEN
          set yyyy =  substr(Digits(inDate),1,4);
          set mm   =  substr(Digits(inDate),5,2);
          set dd   =  substr(Digits(inDate),7,2);
   elseif (fmt = 'cymd' or fmt = '*cl' or fmt = '*CL') THEN
          SET c    = substr(digits(inDate),2,1);
          SET cc   = 19 + dec(c,1,0);
          SET yyyy =  substr(digits(cc),1,2) || substr(digits(inDate),3,2);
          SET mm   =  substr(digits(inDate),5,2);
          SET dd   =  substr(digits(inDate),7,2);
   end if;


    if (yyyy >= '1900' and yyyy <='2200') THEN
       if (mm >= '01' and mm <= '12') THEN
          SET theDate = yyyy || '-' || mm || '-' || '01';
             -- Calc the highest day for the month
          SET testDate = date(theDate) + 1 MONTH - 1 DAY;
          SET maxDay = int(substr(char(testDate),9,2));
          if (dd <= maxDay) THEN
            SET theDate = yyyy || '-' || mm || '-' || dd;
            return date(theDate);
          end if;
       end if;
    end if;

    return date('2000-01-01');

 END;

CREATE  FUNCTION cvtDate(inDate dec(8,0))
 RETURNS DATE
 LANGUAGE SQL
 DETERMINISTIC
   CONTAINS SQL
   SPECIFIC cvtDate_dectodate
   RETURNS NULL ON NULL INPUT
   NO EXTERNAL ACTION
 B:
 BEGIN
     RETURN  cvtDate(inDate, 'ymd');
 END;


CREATE  FUNCTION cvtDate(inDate varchar(128), dateFmt varchar(16))
 RETURNS DATE
 LANGUAGE SQL
 DETERMINISTIC
   CONTAINS SQL
   SPECIFIC cvtDate_chartodate_fmt
   RETURNS NULL ON NULL INPUT
   NO EXTERNAL ACTION
C:
BEGIN
   DECLARE cc        int;
   DECLARE theDigits varchar(128);
   DECLARE theDate   varchar(128);
   DECLARE fmt       varChar(16);

   SET fmt = trim(dateFmt);
   SET theDate = trim(inDate);
   SET cc = 1;
   if (length(theDate) < 5)  THEN
       RETURN CAST(NULL AS DATE);
   end if;

   WHILE cc <= length(theDate) DO
     if SUBSTR(theDate,cc,1) >= '0'
       AND SUBSTR(theDate,cc,1) <= '9' THEN
       SET theDigits = concat(theDigits, SUBSTR(theDate,cc,1));
     end if;
     SET cc = cc + 1;
   END WHILE;
   if (length(theDigits) < 5) THEN
       RETURN CAST(NULL AS DATE);
   end if;
   if (fmt = 'cymdhms' or fmt = '*outfile' and length(theDigits) = 13) THEN
      SET theDigits = substr(theDigits, 1, 7);
      SET fmt = 'cymd';
   end if;

   RETURN  cvtDate(dec(theDigits,8,0), fmt);

 END;

CREATE  FUNCTION cvtDate(inDate varchar(128))
 RETURNS DATE
 LANGUAGE SQL
 DETERMINISTIC
   CONTAINS SQL
   SPECIFIC cvtDate_chartodate
   RETURNS NULL ON NULL INPUT
   NO EXTERNAL ACTION
D:
 BEGIN
     RETURN  cvtDate(inDate, 'ymd');
 END;

 

 

 

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 30 days 11 hours 39 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 30 days 10 hours 48 minutes ago
Edited: Thu, 26 Mar, 2015 at 13:11:37 (734 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 30 days 9 hours ago
Edited: Wed, 27 Feb, 2013 at 10:50:31 (1491 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 30 days 7 hours 34 minutes ago
Edited: Wed, 27 Feb, 2013 at 12:37:23 (1491 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 30 days 6 hours 40 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 29 days 5 hours 44 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 29 days 2 hours 37 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.