create function udffen.dec2datec (arg1 decimal(8,0))
       returns date
       specific dec2datec language sql
       deterministic no external action
       contains sql
-- Convert decimal arguments of format yyyymmdd to a date; 
-- astonishingly, there is no v8 built-in function to do this.
-- Substitute convention '9999-01-01' for invalid dates.
-- Run as db2 -td@ -vf udffen.dec2datec.sql .
-- Alex Levy, January 2005.
-- Sustainable Software Ltd. http://www.sustainablesoftware.net

begin atomic
  declare yyyy, mm, dd smallint ;
  declare mychar char(10) ;
  set yyyy = smallint(floor(arg1 / 10000)) ;
  set mm   = floor( smallint(mod(integer(arg1),10000)) / 100) ;
  set dd   = smallint( mod(integer(arg1),100)) ;
--Convert arg1 to character first; the cast function will not work here as 
--it converts months and days < 10 to single digits.
  set mychar = substr(char(arg1),1,4)||'-'||substr(char(arg1),5,2)||'-'||substr(char(arg1),7,2); 

  return
  case when arg1 > 0
  then case 
       when mm in (01,03,05,07,08,10,12)
	    then case 
		 when dd between 01 and 31
	         then date(mychar)
	         else '9999-01-01'
		 end
       when mm in (04,06,09,11)
	    then case 
	         when dd between 01 and 30
	         then date(mychar)
	         else '9999-01-01'
                 end
       when mm = 02
	    then case
		 when dd between 01 and 28
		 then date(mychar)
                 when dd = 29 
                 then case mod(yyyy,4)
		      when 0 
		      then date(mychar)
		      else '9999-01-01'
                      end
                 else '9999-01-01'
		 end
-- month out of range
       else '9999-01-01'
       end     
-- zero or negative date
  else '9999-01-01'
-- end case
  end ;
-- end compound sql
  end  
@

comment on function udffen.dec2datec is 'Validates and converts decimal integers to dates. Uses convention 9999-01-01 to represent an invalid date value.'
@
