CREATE OR REPLACE FUNCTION mig_db2_ext.mig_db2_fn_date
/* This function is used to address the DATE function of DB2. 
   The input can be
      - a string with an actual length of 7 and the format should be yyyynnn
	    where yyyy(>0) denotes a year and nnn(1-366) denotes a day of that year.
	  - a number
      - a date / timestamp / anything which starts with YYYYxMMxDD, where x can be 
	    hyphon(-), slash(/) or dot(.)  
*/
	( i_date		IN		VARCHAR ) 
RETURN DATE
AS 
	v_idate				VARCHAR(255);
	v_thedate			DATE;                          
	v_chk_number		NUMBER;
	v_yyyy				NUMBER(4);
	v_days              NUMBER(3);
	v_input_type		CHAR(1);
BEGIN 

   v_idate := TRIM(i_date);

   -- If the input is a string with an actual length of 7, the format should be yyyynnn.
   IF LENGTH(v_idate) = 7
   THEN
      BEGIN
		 SELECT LEFT(v_idate, 4), RIGHT(v_idate, 3)
		   INTO v_yyyy, v_days
		   FROM DUAL;
		 
		 IF (v_yyyy > 0) AND (v_days >= 1 AND v_days <= 366)
		 THEN
		    v_input_type := 'S';
		 ELSE
		    v_input_type := 'E';
		 END IF;
	  EXCEPTION
	     WHEN OTHERS THEN
            v_input_type := 'E';
	  END;
	  
	  IF v_input_type = 'S'
	  THEN
         v_thedate := TO_DATE((LPAD(v_yyyy, 4, '0') || '-01-01'), 'YYYY-MM-DD') + (v_days-1);
	     RETURN v_thedate;
	  ELSE
	     RAISE_APPLICATION_ERROR(-20001, 'If the length of input string is 7, the format should be yyyynnn where yyyy(>0) denotes a year and nnn(1-366) denotes a day of that year');  
	  END IF;
   END IF;

   -- Verifying the input is a number
   BEGIN
      SELECT CAST(v_idate AS NUMBER)
        INTO v_chk_number
        FROM DUAL;	

	   v_chk_number := TRUNC(v_chk_number);
	   v_input_type := 'N';
   EXCEPTION
       WHEN OTHERS THEN
		  v_input_type := NULL;
   END;

   -- if it is a number
   IF v_input_type = 'N'
   THEN
   	  v_thedate	:= TO_DATE('0001-01-01', 'YYYY-MM-DD') + (v_chk_number-1);
	  RETURN v_thedate;
   END IF;
   
   -- if the input is started with YYYYxMMxDD
   BEGIN
      SELECT TO_DATE(REPLACE(REPLACE(LEFT(v_idate, 10), '.', '-'), '/', '-'), 'YYYY-MM-DD')
	    INTO v_thedate
		FROM DUAL;
   EXCEPTION
      WHEN OTHERS THEN
	     RAISE_APPLICATION_ERROR(-20001, 'The input should be started with "YYYYxMMxDD" format, where x can be hyphon(-), slash(/) or dot(.)'); 
   END;
  
   RETURN v_thedate;
END;
/

