Exact Months between two dates

[Former Member]

Is there any method to calculate exact months between two dates in SAP Hana sp10 version.

[muthuram_shanmugavel2 · Accepted solution]

Hi Vatsal,

Use this Query and you will get required output

SELECT COUNT(*)-1 AS "MONTHS_BETWEEN" FROM

(

SELECT DISTINCT YEAR, MONTH FROM "_SYS_BI"."M_TIME_DIMENSION"

WHERE DATE_SQL BETWEEN '2000-01-01' AND CURRENT_DATE

)

Regards,

Muthuram

[Former Member · Accepted solution]

This query is not giving the exact month between two dates but it has solved my problem. Thanks.

[nithinu]

Can you try using the below scalar UDF..

CREATE FUNCTION NIT_SCN.MONTHS_BETWEEN(FDATE DATE,TDATE DATE)

RETURNS DIFF DECIMAL(10,8)

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

DEFAULT SCHEMA NIT_SCN AS

BEGIN

  SELECT MON_DIFF +

        CASE WHEN :FDATE <= :TDATE THEN

                  CASE WHEN :FDATE = LAST_DAY(:FDATE) THEN

                            DAYS_BETWEEN(LFDATE,:TDATE)/(DAYS_BETWEEN(LFDATE,LAST_DAY(ADD_MONTHS(LFDATE,1))))

                       ELSE

                            DAYS_BETWEEN(LFDATE,:TDATE)/DAYS_BETWEEN(LFDATE,ADD_MONTHS(LFDATE,1))

                  END

             ELSE

              CASE WHEN :FDATE = LAST_DAY(:FDATE) THEN

                        DAYS_BETWEEN(LFDATE,:TDATE)/(DAYS_BETWEEN(LFDATE,LAST_DAY(ADD_MONTHS(LFDATE,-1)))) * -1

                   ELSE

                        DAYS_BETWEEN(LFDATE,:TDATE)/DAYS_BETWEEN(LFDATE,ADD_MONTHS(LFDATE,-1)) * -1

              END

        END INTO DIFF

  FROM

  (

  SELECT ((TYEAR - FYEAR) * 12) + (TMON - FMON) AS MON_DIFF,

        CASE WHEN :FDATE = LAST_DAY(:FDATE) THEN

        LAST_DAY(ADD_MONTHS(:FDATE, (((TYEAR - FYEAR) * 12) + (TMON - FMON))))

        ELSE

            ADD_MONTHS(:FDATE, (((TYEAR - FYEAR) * 12) + (TMON - FMON)))

        END AS LFDATE

  FROM

  (

  SELECT MONTH(:FDATE) AS FMON,

        YEAR(:FDATE) AS FYEAR,

        CASE WHEN :FDATE < :TDATE AND :FDATE = LAST_DAY(:FDATE) AND :TDATE != LAST_DAY(:TDATE) THEN MONTH(:TDATE) - 1

             WHEN :FDATE < :TDATE AND TO_CHAR(:TDATE,'DD') < TO_CHAR(:FDATE,'DD') THEN MONTH(:TDATE) - 1

             WHEN :FDATE > :TDATE AND TO_CHAR(:TDATE,'DD') > TO_CHAR(:FDATE,'DD') THEN MONTH(:TDATE) + 1

             ELSE MONTH(:TDATE)

        END AS TMON,

        YEAR(:TDATE) AS TYEAR

  FROM DUMMY

  )

  );

END;

Regards,

Nithin

[chandan_praharaj]

can you please check my blog on this

[nithinu]

Can you try the below. Assume that each month is having 30 days

SELECT FLOOR(DAYS_BETWEEN( TO_DATE('2016-01-31','YYYY-MM-DD'),TO_DATE('2016-03-30','YYYY-MM-DD')) / 30 )

FROM DUMMY;

Regards,

Nithin

[Former Member]

The assumption of 30 days is not possible in my case.

[Former Member]

I don't see any direct formula to do this. I guess the straight forward way of getting the days between the two dates and dividing by 30 is not what you want. In that case, if you are doing this in a calculation/analytic view, you can use the _SYS_BI.M_TIME_DIMENSION table to get the YEAR and MONTH of the particular dates, say DATE1 and DATE2. Then you can use a calculated column with a formula like below to get the difference in months(year1 and month1 correspond to DATE1 and year2 and month2 correspond to DATE2):

if(year1 = year2,

    if(month1 > month2,

    month1 - month2,

    month2 - month1

        ),

    if(year1 > year2,

    (year1 - year2)*12+month1-month2,

    (year2 - year1)*12+month2-month1)

)

However, if you want to do this through SQL query, say in a SQL Script, then you can use a query like below

select

case

when year(DATE1) = year(DATE2)

then

    case

    when month(DATE1) > month(DATE2)

    then month(DATE1) - month(DATE2)

    else

    month(DATE2) - month(DATE1)

    end

when year(DATE1) > year(DATE2)

then

(year(DATE1) - year(DATE2))*12 + month(DATE1) - month(DATE2)

else

(year(DATE2) - year(DATE1))*12 + month(DATE2) - month(DATE1)

end

as no_of_months

from dummy

:

Regards,

Amit