-- Convert integer representations of yyyymmdd dates into the date datatype.
-- If an invalid date argument is received, return '9999-01-01' or whatever local convention is used to represent null or invalid dates.
-- The alternative is to RAISE_ERROR().
-- Alex Levy, Sustainable Software Ltd. http://www.sustainablesoftware.net
-- January 2005.

create function udffen.int2date (arg1 integer)
       returns date
       specific int2date language sql
       deterministic no external action
       contains sql
       return

  case when arg1 > 0
  then case 
       when substr(char(arg1),5,2) in ('01','03','05','07','08','10','12')
	          then case 
		             when substr(char(arg1),7,2) between '01' and '31'
		                  then date(substr(char(arg1),1,4)||'-'||substr(char(arg1),5,2)||'-'||substr(char(arg1),7,2))
		             else '9999-01-01'
		   end
       when substr(char(arg1),5,2) in ('04','06','09','11')
	          then case 
		             when substr(char(arg1),7,2) between '01' and '30'
		                  then date(substr(char(arg1),1,4)||'-'||substr(char(arg1),5,2)||'-'||substr(char(arg1),7,2))
		             else '9999-01-01'
		   end
       when substr(char(arg1),5,2) = '02'
	          then case
		             when substr(char(arg1),7,2) between '01' and '28'
		                  then date(substr(char(arg1),1,4)||'-'||substr(char(arg1),5,2)||'-'||substr(char(arg1),7,2))
                 when substr(char(arg1),7,2) = '29' 
                      then case mod(arg1,4)
		             when 0 then date(substr(char(arg1),1,4)||'-'||substr(char(arg1),5,2)||'-'||substr(char(arg1),7,2))
			           else '9999-01-01'
                 end
       end
       else '9999-01-01'
  end     
       else '9999-01-01'
  end
;
comment on function udffen.int2date is 'Fenced. Validates and converts integers to dates. Returns 9999-01-01 to represent an invalid date value in the argument.'
;
