Skip to Content
author's profile photo Former Member
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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • 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

    Add a comment
    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)
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.