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;