How to calculate years between two dates

[Former Member]

I need to find a person's age.  Any suggestions on how to consider leap years in my calculation?

Thanks.

[muthuram_shanmugavel2]

Hi,

Create this Procedure.

You can get the Age of Person correctly.

CREATE PROCEDURE YEARS_BETWEEN_CALC (IN FROM_DATE DATE, OUT AGE Integer)

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

AS

BEGIN

Declare VAR_CURRENT_YEAR Integer;

Declare VAR_YEAR_CALC DATE;

Declare VAR_MONTH_DATE Varchar(6);

SELECT YEAR(CURRENT_DATE) INTO VAR_CURRENT_YEAR FROM DUMMY;

VAR_MONTH_DATE = RIGHT(FROM_DATE,6);

VAR_YEAR_CALC = to_date(:VAR_CURRENT_YEAR || :VAR_MONTH_DATE);

IF CURRENT_DATE >= :VAR_YEAR_CALC THEN

SELECT COUNT(*)-1 AS "YEARS_BETWEEN" INTO AGE FROM

(

SELECT DISTINCT YEAR FROM "_SYS_BI"."M_TIME_DIMENSION"

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

);

ELSE

SELECT COUNT(*)-2 AS "YEARS_BETWEEN" INTO AGE FROM

(

SELECT DISTINCT YEAR FROM "_SYS_BI"."M_TIME_DIMENSION"

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

);

END IF;

END

Results:

P.S:

Generate Time Data in "_SYS_BI"."M_TIME_DIMENSION"

[lbreddemann]

Hmm... do you get paid to increase power consumption and execution time for queries?

Also:

Why all this text parsing?

Why relying on implicit type conversion and the auxiliary table content?

Why not using the standard function?

And if you really have to have a custom function - why go for a procedure with an out parameter?

[muthuram_shanmugavel2]

Hi Lars,

I am using SP10 System - Version: 1.00.102.00.1442292917

Standard Function YEARS_BETWEEN not working on my system.

SELECT YEARS_BETWEEN (TO_DATE('2001-10-13'), TO_DATE('2003-01-14')) "years_between" FROM DUMMY;

Could not execute 'SELECT YEARS_BETWEEN(TO_DATE('2001-10-13'), TO_DATE('2003-01-14')) "years_between" FROM DUMMY'

SAP DBTech JDBC: [328]: invalid name of function or procedure: YEARS_BETWEEN: line 1 col 8 (at pos 7)

I think YEARS_BETWEEN - standard function would be supported in latest versions.

So I thought of creating custom procedure for older versions and

used text parsing and auxiliary table content to get required output.

As per your suggestion, I will go for custom function approach.

Thanks & Regards,

Muthuram

[chandan_praharaj]

Hi Lars,

Even I have tried the same in SP11 box. And facing the same issue. So unfortunately, we can only go with the implementation, which Muthu has given.

Regards,

Chandan

[lbreddemann]

Hi Chandan

it's true that this function is available with SPS 12 onwards and not in earlier releases.

That however doesn't mean you have to take the provided code.

A less convoluted approach would be this:

create function years_between (in d1 date, in d2 date)

returns  years_between INTEGER

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER AS

begin

-- two step approach:

-- first see the difference in the YEAR component

-- second see if MMDD of d2 is larger than MMDD of d1

-- this is to see if the day of the year has already passed or is still to come

-- if the day has passed already, the last year counts, else it doesn't

declare y1, y2 integer;

declare yeardiff integer;

declare mmdd1, mmdd2 integer;

declare tempdate date;

-- check if either of the entries is NULL

   if coalesce(:d1, :d2) IS NULL then

        years_between := NULL;

   end if;

-- get dates in order. d1 should be smaller than d2

    if (:d1 > :d2) then -- get dates in order

        tempdate := :d1;

        d1 := :d2;

        d2 := :tempdate;

    end if;

-- year value extraction

    y1 := YEAR (d1);

    y2 := YEAR (d2);

-- assign the MMDD values, multiply MM by 100 to perform shift to the left

    mmdd1 := MONTH(:d1) * 100 + DAYOFMONTH(:d1);

    mmdd2 := MONTH(:d2) * 100 + DAYOFMONTH(:d2);

    yeardiff := :y2 - :y1;

    if (:mmdd2 < :mmdd1)

        AND (:yeardiff >0) then -- last year is not completed yet

        yeardiff = :yeardiff -1;

    end if;

    years_between := :yeardiff;

end;

I'd argue that this function is technology wise more efficient as it avoids unnecessary type casting and SELECT statements as far as possible.

Also, it provides the same semantics as the SPS12 function, which allows for an easy transition.

[muthuram_shanmugavel2]

Hi Lars,

Thanks you

UDF Execution & Results:

SELECT "MS_SCHEMA"."YEARS_BETWEEN" ('2000-02-29', '2017-03-01').years_between As "YEARS_BETWEEN" FROM DUMMY

SELECT "MS_SCHEMA"."YEARS_BETWEEN" ('2000-02-29', '2017-02-28').years_between As "YEARS_BETWEEN" FROM DUMMY

Regards,

Muthuram

[lbreddemann]

The nice part is that you can actually write it like this:

SELECT YEARS_BETWEEN (date'2000-02-29', date'2017-02-28') As "YEARS_BETWEEN"

FROM DUMMY;

[former_member182302]

YEARS_BETWEEN - SAP HANA SQL and System Views Reference - SAP Library

Have a look on the above.

Regards,

Krishna Tangudu