Former Member

# How to calculate years between two dates

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

Thanks.

10|10000 characters needed characters exceeded

### Related questions

• Posted on Jul 15, 2016 at 10:25 PM

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

Have a look on the above.

Regards,

Krishna Tangudu

10|10000 characters needed characters exceeded
• Posted on Jul 18, 2016 at 05:04 AM

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"

temp.PNG (5.1 kB)
temp.PNG (4.9 kB)
temp.PNG (4.5 kB)