예전에 Oracle에는 MONTHS_BETWEEN 이란 함수가 있어 개월 수를

계산에 사용했는데, Informix에는 없어 제가 한 방법은 Stored procedure를 만들어 SQL에서 사용했었습니다.

/*@****************************************************************************/
/* 1. 프로그램 ID : MONTHS_BETWEEN                                            */
/* 2. 유       형 : Stored Procedure                                          */
/* 3. 기 능 정 의 :                                                           */
/*                  기간에 대한 월수를 계산한다.                              */
/* 4. 입 력 변 수 :                                                           */
/*                  시작일자(fr_dt )                                          */
/*                  종료일자(to_dt )                                          */
/* 5. 출 력 변 수 :                                                           */
/*                  월    수(mm_cnt)                                          */
/* 6. 변 경 이 력 :                                                           */
/*----------------------------------------------------------------------------*/
/* 작성자  소     속  일      자                 내      용                   */
/* ------  ---------  ----------  ------------------------------------------- */
/* 홍길동  xxxx/xxxx  2001. 1. 8  최초작성                                    */
/*@****************************************************************************/
DROP   PROCEDURE MONTHS_BETWEEN;
/*----------------------------------------------------------------------------*/
/*             P R O C E D U R E                       S T A R T              */
/*----------------------------------------------------------------------------*/
CREATE PROCEDURE MONTHS_BETWEEN(fr_dt  char(8), to_dt  char(8))

RETURNING int;
/*----------------------------------------------------------------------------*/
/* 변수 선언.                                                                 */
/*----------------------------------------------------------------------------*/
DEFINE ii        int;                                      /* Index           */
DEFINE fr_yy     int;                                      /* 시작년도        */
DEFINE fr_mm     int;                                      /* 시작월          */
DEFINE fr_dd     int;                                      /* 시작일자        */
DEFINE to_yy     int;                                      /* 종료년도        */
DEFINE to_mm     int;                                      /* 종료월          */
DEFINE to_dd     int;                                      /* 종료일자        */
DEFINE mm_cnt    int;                                      /* 월수            */
DEFINE ft_dt     char(8);                                  /* 일자(Temp)      */
/*----------------------------------------------------------------------------*/
/* 변수 초기화.                                                               */
/*----------------------------------------------------------------------------*/
LET ii     = 0;                                            /* Index           */
LET fr_yy  = 0;                                            /* 시작년도        */
LET fr_mm  = 0;                                            /* 시작월          */
LET fr_dd  = 0;                                            /* 시작일자        */
LET to_yy  = 0;                                            /* 종료년월        */
LET to_mm  = 0;                                            /* 종료월          */
LET to_dd  = 0;                                            /* 종료일자        */
LET mm_cnt = 0;                                            /* 월수            */
LET ft_dt  = '';                                           /* 일자(Temp)      */
/*----------------------------------------------------------------------------*/
/* 시작일자가 종료일자 보다 큰 경우.                                          */
/*----------------------------------------------------------------------------*/
IF ((fr_dt < '19000101') OR (to_dt < '19000101')) THEN
    RETURN mm_cnt;
END IF;
/*----------------------------------------------------------------------------*/
/* 시작일자가 종료일자 보다 큰 경우.                                          */
/*----------------------------------------------------------------------------*/
IF  fr_dt > to_dt THEN
    LET ft_dt = fr_dt;
    LET fr_dt = to_dt;
    LET to_dt = ft_dt;
END IF;
/*----------------------------------------------------------------------------*/
/* 년월일 분리.                                                               */
/*----------------------------------------------------------------------------*/
LET fr_yy = fr_dt[1,4];
LET fr_mm = fr_dt[5,6];
LET fr_dd = fr_dt[7,8];
LET to_yy = to_dt[1,4];
LET to_mm = to_dt[5,6];
LET to_dd = to_dt[7,8];
/*----------------------------------------------------------------------------*/
/* 시작년월과 종료년월이 동일한 경우.                                         */
/*----------------------------------------------------------------------------*/
IF  fr_dt[1,6] = to_dt[1,6] THEN
    IF  fr_dd >= to_dd THEN
        LET mm_cnt = 0;
    ELSE
        LET mm_cnt = 1;
    END IF;
/*----------------------------------------------------------------------------*/
/* 시작일자와 종료일자가 동일한 값이 아닌 경우.                               */
/*----------------------------------------------------------------------------*/
ELSE
    /*------------------------------------------------------------------------*/
    /* 년도 차이에 대한 월수 계산.                                            */
    /*------------------------------------------------------------------------*/
    LET mm_cnt = (to_yy - fr_yy) * 12;
    /*------------------------------------------------------------------------*/
    /* 월 차이에 대한 월수 계산.                                              */
    /*------------------------------------------------------------------------*/
    IF  to_dd >= fr_dd THEN
        LET mm_cnt = mm_cnt + (to_mm - fr_mm);
    ELSE
        LET mm_cnt = mm_cnt + (to_mm - fr_mm) - 1;
    END IF;
END IF;
/*----------------------------------------------------------------------------*/
/* 시작일자가 종료일자보다 큰 경우 음수반영.                                  */
/*----------------------------------------------------------------------------*/
IF  ft_dt > ' ' THEN
    LET mm_cnt = mm_cnt * - 1;
END IF;
/*----------------------------------------------------------------------------*/
/* 월수 Return.                                                               */
/*----------------------------------------------------------------------------*/
RETURN mm_cnt;

END    PROCEDURE;

답변감사합니다.

Stored procedure 말고 자체적으로 지원되는 함수나

기타 다른방법은 없을까요?

혹시 interval 에 관련된 자세하게 설명된 자료없을까요?

이곳에서 검색해봤는데.....왠지 어렵게 설명된거 같아서요