Age Calculation duplicate column using sql server 2008 R2

Question

I have login table with DOB Column. I need duplicate column for age. But i can't do this. I can convert DOB column convert to Age for alone

`SELECT FLOOR(DATEDIFF(DAY,'10/10/1990' , getdate()) / 365.25)` works fine.

But I need to convert whole column.

If I'm Using

SELECT FLOOR(DATEDIFF(DAY,Select DOB From login_tbl , getdate()) / 365.25) like this, 

It's throwing error. How can I get it?

Thankyou

Answer 1

Your approach will get wrong result in such cases below:

declare @now date
set  @now = '11/10/2014'
select 
  FLOOR(DATEDIFF(DAY,'11/10/2013' ,  @now) / 365.25) -- should be 1 but will get 0
, FLOOR(DATEDIFF(DAY,'11/10/2012' ,  @now) / 365.25) -- should be 2 but will get 1
, DATEDIFF(DAY,'11/10/2013' ,  @now)
, DATEDIFF(DAY,'11/10/2012' ,  @now)

My suggestion is :

declare @now date
set  @now = '11/10/2014'
select (convert(int,convert(varchar(8), @now ,112)) 
      - convert(int,convert(varchar(8),convert(date,'11/10/2013'),112) ) )/10000

You could see my explain in this answer.

So for your login_tbl you could:

select 
  DOB, (convert(int,convert(varchar(8), DOB ,112)) 
      - convert(int,convert(varchar(8),convert(date,'11/10/2013'),112) ) )/10000 as AGE
from
  login_tbl

Answer 2

select trunc((trunc(sysdate) - to_date('16-mar-2010', 'dd-mon-yyyy'))/ (365.23076923074)) 
from dual