I am trying to calculate the age of an individual.

I would like the age to be calculated to the exact as possible. However, if the age is on the same date, it should calculate the age assume it's already his birthday.

EXAMPLE: Today date: Dec 15, 2020
user #1: DOB: Dec 14, 2005 (AGE: 15)
user #2: DOB: Dec 16, 2005 (AGE: 14)
user #3: DOB: Dec 15, 2005 (AGE: 15)

I have this query, but for user #3, it is giving me age 14 when it should be 15.

SELECT
USER,
DATE_OF_BIRTH, 
FLOOR(DATEDIFF(DAY, B.DATE_OF_BIRTH, GETDATE())/365.25) AS CALCULATED_AGE
FROM TBL_A

CodePudding user response：

I think you could do it using the MONTHS_BETWEEN function.

select floor(months_between(getdate(), date_of_birth) / 12) from tbl_a

CodePudding user response：

I have to assume this question is not being done in Snowflake, as the results are off by one.

with mytable(user, dob, age_expected) as (
    select * from values
         (1, '2005-12-14'::date, 15),
         (2, '2005-12-16'::date, 14),
         (3, '2005-12-15'::date, 15)
)
SELECT
    user
    ,dob 
    ,'2022-12-15'::date as todays_date
    ,DATEDIFF(DAY, dob, todays_date) as gap_days
    ,gap_days/365.25 as d365
    ,floor(d365) as f0
    ,FLOOR(DATEDIFF(DAY, dob, todays_date)/365.25) AS CALCULATED_AGE
FROM mytable

So if we deconstruct what is happening, move the month of interest back to november so the before after months case can be seen, and move the "current date" back 2 years so we get the stated expected results 14/15:

with mytable(user, dob, age_expected) as (
    select * from values
         (1, '2005-10-15'::date, 15),    

         (2, '2005-11-14'::date, 15),
         (3, '2005-11-16'::date, 14),
         (4, '2005-11-15'::date, 15),

         (5, '2005-12-15'::date, 14)
)
SELECT
    user
    ,dob 
    ,age_expected
    ,'2020-11-15'::date as todays_date
    ,year(dob) as dob_y
    ,month(dob) as dob_m
    ,day(dob) as dob_d
    ,year(todays_date) as today_y
    ,month(todays_date) as today_m
    ,day(todays_date) as today_d
    ,DATEDIFF(DAY, dob, todays_date)/365.25 as float_val
    ,today_y - dob_y as y_dif
        ,case 
        when today_m < dob_m then 'a'
        when today_m > dob_m then 'b'
        when today_d < dob_d then 'c'
        when today_d >= dob_d then 'd'
    end as pick
    ,case 
        when today_m < dob_m then y_dif -1 
        when today_m > dob_m then y_dif
        when today_d < dob_d then y_dif -1 
        when today_d >= dob_d then y_dif
    end as result

FROM mytable
order by dob

So using the numbers you state, would would, but it we move the current date back to 2022 those numbers don't work, due to the stride of actual number of daylight days. so you just stick to the partial math method which can be simplefied to:

with mytable(user, dob, age_expected) as (
    select * from values
         (1, '2005-11-15'::date, 15),    

         (2, '2005-12-14'::date, 15),
         (3, '2005-12-16'::date, 14),
         (4, '2005-12-15'::date, 15),

         (5, '2006-01-15'::date, 14)
)
SELECT
    user
    ,dob 
    ,age_expected
    ,CURRENT_DATE
    ,case 
        when month(CURRENT_DATE) < month(dob) then year(CURRENT_DATE) - year(dob) -1 
        when month(CURRENT_DATE) > month(dob) then year(CURRENT_DATE) - year(dob)
        when day(CURRENT_DATE) < day(dob) then year(CURRENT_DATE) - year(dob) -1 
        when day(CURRENT_DATE) >= day(dob) then year(CURRENT_DATE) - year(dob)
    end as result

FROM mytable
order by dob

gives: