Age Calculation

Database
Enthusiast

Re: Age Calculation

The formula above does not exactly compute the age to the day, because it assumes there is 1/4th of a extra day for every year of life, but when the person is under 4, there may be no leap years at all.

Enthusiast

Re: Age Calculation

This does work to the exact date for specific dates:

SELECT (EXTRACT(YEAR FROM DATE '2012-01-04') - EXTRACT(YEAR FROM DATE '1989-01-04') (NAMED YEARS))

      + CASE WHEN DATE '2012-01-04' - (YEARS (INTERVAL YEAR)) < DATE '1989-01-04'

             THEN -1 ELSE 0 END AS age_whole_yrs;

Or for use with the current date and a birth date in your database:

SELECT (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM brth_dt) (NAMED YEARS))

      + CASE WHEN CURRENT_DATE - (YEARS (INTERVAL YEAR)) < brth_dt

             THEN -1 ELSE 0 END AS age_whole_yrs;

Enthusiast

Re: Age Calculation

One additional note:  the formula results in an overflow error for ages over 100, so you have to handle that age range separately.

Highlighted
Enthusiast

Re: Age Calculation

  Hi,

 i need the diff between two dates in same date format  

Eg:

10-10-2012, 5-6-2011  the diff of these two dates results should be in ddmmyy format can any one help me please

Fan

Re: Age Calculation

Here's the change to make this work for ages > 100.  Added "(3)" for precision. 

SELECT (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM brth_dt) (NAMED YEARS))

      + CASE WHEN CURRENT_DATE - (YEARS (INTERVAL YEAR(3))) < brth_dt

             THEN -1 ELSE 0 END AS age_whole_yrs

Fan

Re: Age Calculation

Here's so it works on leap years ( i.e. current_date = date'2012-02-29' )

SELECT (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM brth_dt) (NAMED YEARS))

      + CASE WHEN add_months( CURRENT_DATE , - YEARS * 12 < brth_dt

             THEN -1 ELSE 0 END AS age_whole_yrs

Not applicable

Re: Age Calculation

select cast(cast('2006-10-05' as date) as integer) - cast(cast('1976-10-05' as date) as integer) Diff,

       cast(diff/10000 as integer) as AGE

Not applicable

Re: Age Calculation

I have a requirement where in I need to get the counts of expected product aging(churn date each day) for a period of 3 months (in 120 days). I have attached the expected result of the requirement. Any help will be appreciated. Thanks! -stan

Re: Age Calculation

Calculating difference between two dates in terms of No of years, months and days are somthing tricky in Teradata - Though years and month calculation logic would pass maximum hurdles but number of days left after calculating year and month seems to be little uneasy because of variation of number of days in a month, Here is a sample code to calculate difference between two dates in term of Years, months and Days -

Step 1 - Create the macro as below -

 Follow the attachment -

Step 2- Execute the macro passing two dates in 'YYYYMMDD' format -

EXEC D1DCFEDW.CTLFW_TEST ('20110330', '20111201');

Now here we go ................

Thats all...Thanks!

Re: Age Calculation

@stan_jobson, how you are calculating aging? Could u pls elaborate ur requirement so that it can be addressed better...