--DROP FUNCTION DateAdd
!

CREATE FUNCTION "DateAdd" ( IN "DatePart" VARCHAR,
                            IN "Number"   INTEGER,
                            IN "Value"    TIMESTAMP) RETURNS TIMESTAMP
BEGIN
  DECLARE Result            TIMESTAMP;
  DECLARE dDatePart         VarCHAR;
  DECLARE _Year             INTEGER;
  DECLARE _Month            INTEGER;
  DECLARE _Day              INTEGER;
  DECLARE _Hour             INTEGER;
  DECLARE _Minute           INTEGER;
  DECLARE _Second           INTEGER;
  DECLARE _MSecond          INTEGER;
  DECLARE DaysInMonth       INTEGER;
  DECLARE InitialValue      INTEGER;
  DECLARE Interim           INTEGER;
  DECLARE LoopControl       BOOLEAN DEFAULT TRUE;
  DECLARE WasLastDayInMonth BOOLEAN DEFAULT FALSE;
  SET dDatePart = Upper(DatePart);
  SET _Year     = EXTRACT(YEAR FROM Value);
  SET _Month    = EXTRACT(MONTH FROM Value);
  SET _Day      = EXTRACT(DAY FROM Value);
  SET _Hour     = EXTRACT(HOUR FROM Value);
  SET _Minute   = EXTRACT(MINUTE FROM Value);
  SET _Second   = EXTRACT(SECOND FROM Value);
  SET _MSecond  = EXTRACT(MSECOND FROM Value);
  IF
    _Month IN (1, 3, 5, 7, 8, 10, 12)
  THEN
    SET DaysInMonth = 31;
  ELSEIF
    _Month IN (4, 6, 9, 11)
  THEN
    SET DaysInMonth = 30;
  ELSEIF
    (_Month           = 2)
    AND (_Year MOD 4) = 0
  THEN
    SET DaysInMonth = 29;
  ELSE
    SET DaysInMonth = 28;
  END IF;
  IF
    _Day = DaysInMonth
  THEN
    SET WasLastDayInMonth = TRUE;
  END IF;
  CASE
  WHEN
    (dDatePart   ='YEAR')
    OR (dDatePart='YYYY')
    OR (dDatePart='YY')
  THEN
    SET _Year = _Year + Number;
  WHEN
    (dDatePart   ='MONTH')
    OR (dDatePart='MM')
    OR (dDatePart='M')
  THEN
    SET _Month = _Month + Number;
  WHEN
    (dDatePart   ='DAY')
    OR (dDatePart='DD')
    OR (dDatePart='D')
  THEN
    SET _Day = _Day + Number;
  WHEN
    (dDatePart   ='HOUR')
    OR (dDatePart='HH')
  THEN
    SET _Hour = _Hour + Number;
  WHEN
    (dDatePart   ='MINUTE')
    OR (dDatePart='MI')
    OR (dDatePart='N')
  THEN
    SET _Minute = _Minute + Number;
  WHEN
    (dDatePart   ='SECOND')
    OR (dDatePart='SS')
    OR (dDatePart='S')
  THEN
    SET _Second = _Second + Number;
  WHEN
    (dDatePart   ='MILLISECOND')
    OR (dDatePart='MS')
  THEN
    SET _MSecond = _MSecond + Number;
  END CASE;
  IF
    WasLastDayInMonth
    AND ((dDatePart='MONTH')
    OR (dDatePart  ='MM')
    OR (dDatePart  ='M'))
  THEN
    IF
      _Month IN (1, 3, 5, 7, 8, 10, 12)
    THEN
      SET DaysInMonth = 31;
    ELSEIF
      _Month IN (4, 6, 9, 11)
    THEN
      SET DaysInMonth = 30;
    ELSEIF
      (_Month           = 2)
      AND (_Year MOD 4) = 0
    THEN
      SET DaysInMonth = 29;
    ELSE
      SET DaysInMonth = 28;
    END IF;
    SET _Day = DaysInMonth;
  END IF;
    BEGIN
      SET Result = CAST( CAST(_Year AS VARCHAR(4)) +'-'+ CAST(_Month AS VARCHAR(2)) +'-'+ CAST(_Day AS VARCHAR(2)) +' '+ CAST(_Hour AS VARCHAR(2)) +':'+ CAST(_Minute AS VARCHAR(2)) +':'+ CAST(_Second AS VARCHAR(2)) +'.'+ CAST(_MSecond AS VARCHAR(20)) AS TIMESTAMP );
    EXCEPTION
      /*----------------------- TIME PORTION -----------------------*/
      IF
        _MSecond > 1000
      THEN
        SET _Second  = _Second  + FLOOR(_MSecond / 1000);
        SET _MSecond = _MSecond - (1000 * FLOOR(_MSecond / 1000));
      ELSEIF
        _MSecond < 0
      THEN
        SET InitialValue = _MSecond     + ABS(Number);
        SET Interim      = (1           + FLOOR(ABS(_MSecond) / 1000));
        SET _Second      = _Second      - Interim;
        SET _MSecond     = InitialValue - ABS(Number) + ABS(Interim * 1000);
      END IF;
      IF
        _Second > 60
      THEN
        SET _Minute = _Minute + FLOOR(_Second / 60);
        SET _Second = _Second - (60 * FLOOR(_Second / 60));
      ELSEIF
        _Second < 0
      THEN
        SET InitialValue = _Second      + ABS(Number);
        SET Interim      = (1           + FLOOR(ABS(_Second) / 60));
        SET _Minute      = _Minute      - Interim;
        SET _Second      = InitialValue - ABS(Number) + ABS(Interim * 60);
      END IF;
      IF
        _Minute > 60
      THEN
        SET _Hour   = _Hour   + FLOOR(_Minute / 60);
        SET _Minute = _Minute - (60 * FLOOR(_Minute / 60));
      ELSEIF
        _Minute < 0
      THEN
        SET InitialValue = _Minute      + ABS(Number);
        SET Interim      = (1           + FLOOR(ABS(_Minute) / 60));
        SET _Hour        = _Hour        - Interim;
        SET _Minute      = InitialValue - ABS(Number) + ABS(Interim * 60);
      END IF;
      IF
        _Hour > 24
      THEN
        SET _Day  = _Day  + FLOOR(_Hour / 24);
        SET _Hour = _Hour - (24 * FLOOR(_Hour / 24));
      ELSEIF
        _Hour < 0
      THEN
        SET InitialValue = _Hour        + ABS(Number);
        SET Interim      = (1           + FLOOR(ABS(_Hour) / 24));
        SET _Day         = _Day         - Interim;
        SET _Hour        = InitialValue - ABS(Number) + ABS(Interim * 24);
      END IF;
      /*----------------------- END OF TIME PORTION -----------------------*/
      /*----------------------- DATE PORTION -----------------------*/
      IF
        _Day > DaysInMonth
      THEN
        WHILE LoopControl DO
        IF
          _Month IN (1, 3, 5, 7, 8, 10, 12)
        THEN
          SET DaysInMonth = 31;
        ELSEIF
          _Month IN (4, 6, 9, 11)
        THEN
          SET DaysInMonth = 30;
        ELSEIF
          (_Month           = 2)
          AND (_Year MOD 4) = 0
        THEN
          SET DaysInMonth = 29;
        ELSE
          SET DaysInMonth = 28;
        END IF;
        IF
           (_Day - DaysInMonth) > 0
        THEN
          SET _Month = _Month + 1;
          SET _Day   = _Day   - DaysInMonth;
        ELSE
          SET LoopControl = FALSE;
        END IF;
      END WHILE;
      ELSEIF _Day <= 0 THEN WHILE _Day <= 0 DO SET _Month = _Month - 1;
      IF
        _Month < 1
      THEN
        SET _Year  = _Year - 1;
        SET _Month = 1;
      END IF;
      IF
        _Month IN (1, 3, 5, 7, 8, 10, 12)
      THEN
        SET DaysInMonth = 31;
      ELSEIF
        _Month IN (4, 6, 9, 11)
      THEN
        SET DaysInMonth = 30;
      ELSEIF
        (_Month           = 2)
        AND (_Year MOD 4) = 0
      THEN
        SET DaysInMonth = 29;
      ELSE
        SET DaysInMonth = 28;
      END IF;
      SET _Day = _Day + DaysInMonth;
    END WHILE;
  END IF;
  IF
    _Month > 12
  THEN
    SET _Year  = _Year  + FLOOR(_Year / 12);
    SET _Month = _Month - (12 * FLOOR(_Month / 12));
  ELSEIF
    _Month < 0
  THEN
    SET InitialValue = _Month       + ABS(Number);
    SET Interim      = (1           + FLOOR(ABS(_Month) / 12));
    SET _Year        = _Year        - Interim;
    SET _Month       = InitialValue - ABS(Number) + ABS(Interim * 12);
  ELSE
    IF
      _Month IN (1, 3, 5, 7, 8, 10, 12)
    THEN
      SET DaysInMonth = 31;
    ELSEIF
      _Month IN (4, 6, 9, 11)
    THEN
      SET DaysInMonth = 30;
    ELSEIF
      (_Month           = 2)
      AND (_Year MOD 4) = 0
    THEN
      SET DaysInMonth = 29;
    ELSE
      SET DaysInMonth = 28;
    END IF;
    IF
      _Day > DaysInMonth
    THEN
      SET _Day = DaysInMonth;
    END IF;
  END IF;
  IF
    WasLastDayInMonth
    AND ((dDatePart='MONTH')
    OR (dDatePart  ='MM')
    OR (dDatePart  ='M'))
  THEN
    IF
      _Month IN (1, 3, 5, 7, 8, 10, 12)
    THEN
      SET DaysInMonth = 31;
    ELSEIF
      _Month IN (4, 6, 9, 11)
    THEN
      SET DaysInMonth = 30;
    ELSEIF
      (_Month           = 2)
      AND (_Year MOD 4) = 0
    THEN
      SET DaysInMonth = 29;
    ELSE
      SET DaysInMonth = 28;
    END IF;
    SET _Day = DaysInMonth;
  END IF;
  /*----------------------- END OF DATE PORTION -----------------------*/
  SET Result = CAST( CAST(_Year
AS
  VARCHAR(4)) +'-'+ CAST(_Month AS VARCHAR(2)) +'-'+ CAST(_Day AS VARCHAR(2)) +' '+ CAST(_Hour AS VARCHAR(2)) +':'+ CAST(_Minute AS VARCHAR(2)) +':'+ CAST(_Second AS VARCHAR(2)) +'.'+ CAST(_MSecond AS VARCHAR(20)) AS TIMESTAMP );
END;
RETURN Result;
END !