Age Calculation Query

Question

Possible Duplicate:
How to calculate age in T-SQL with years, months, and days
calculating age from sysdate and birthdate using SQL Server

In the process of some age calculations. I'm a bit of a novice when it comes to SQL but I've been tasked with singling out persons who will turn 65yrs old as of 31 March 2013. So far I'm able to calculate age based on given DOB, however I'm having issues singling out these persons. I'm thinking this is a simple issue but I'm stumped, see sql statement. Can someone please point me in the right direction.

SELECT ip_master.ssn, ip_master.firstname, ip_master.surname, ip_master.status,

CASE WHEN DATEADD(YEAR, DATEDIFF (YEAR, dob, '2013/03/31'), dob) > '2013/03/31'
THEN DATEDIFF(YEAR, dob, '2013/03/31') - 1
ELSE DATEDIFF(YEAR, dob, '2013/03/31')
END AS 'Age'

FROM test_db.dbo.ip_master ip_master

Assuming that the question refers to people turning 65 this year, no later than March 31st, `where DateAdd( year, -65, '2013-01-01' ) <= DoB and DoB <= DateAdd( year, -65, '2013-03-31' )` should do. — HABO, Jan 22 '13 at 19:15

mechanical_meat · Answer 1 · 2013-01-22T18:17:00.853

2

Append a WHERE clause to your statement:

...
WHERE dob = '1948-03-31'

edited Jan 22 '13 at 18:17

answered Jan 22 '13 at 18:02

mechanical_meat

163,903
24
228
223

`WHERE dob = date '1948-03-31'`?, what syntax is that? – Lamak Jan 22 '13 at 18:14
@Lamak, thanks. The syntax was updated to be ANSI-compliant. – mechanical_meat Jan 22 '13 at 18:20
Thanks alot guys, I knew I was over thinking this. Was trying to use the WHERE clause on the Age column but was not sure what esle to do after it didn't work. – Garrett Jones Jan 22 '13 at 18:49

score 1 · Answer 2 · answered Jan 22 '13 at 18:02

Would the following do? I just buried your query inside another query and applied restriction on age.

select * from
(
SELECT ip_master.ssn, ip_master.firstname, ip_master.surname, ip_master.status,

CASE WHEN DATEADD(YEAR, DATEDIFF (YEAR, dob, '2013/03/31'), dob) > '2013/03/31'
THEN DATEDIFF(YEAR, dob, '2013/03/31') - 1
ELSE DATEDIFF(YEAR, dob, '2013/03/31')
END AS 'Age'

FROM test_db.dbo.ip_master ip_master
)
where Age >= 65

Abhishek kumar · Answer 3 · 2013-01-22T18:27:48.773

0

Whatever you have done is correct. For singling out you need a where clause.

SELECT ip_master.ssn, ip_master.firstname, ip_master.surname, ip_master.status,

CASE WHEN DATEADD(YEAR, DATEDIFF (YEAR, dob, '2013/03/31'), dob) > '2013/03/31'
THEN DATEDIFF(YEAR, dob, '2013/03/31') - 1
ELSE DATEDIFF(YEAR, dob, '2013/03/31')
END AS 'Age'
FROM test_db.dbo.ip_master ip_master
WHERE dob = '1948-03-31'

edited Jan 22 '13 at 18:27

answered Jan 22 '13 at 18:03

Abhishek kumar

2,586
5
32
38

`CEIL` is not a valid SQL Server function. Did you mean `CEILING`?, and what would be the point of using it on an `INT` column? – Lamak Jan 22 '13 at 18:14
yeah I meant CEILING. Is Age INT? What will be a person's age as of 31 JAN 2013? – Abhishek kumar Jan 22 '13 at 18:20
Well, `Age` is a calculation that takes the result of `DATEDIFF`, so yes, its an `INT`. That person's age in years if it was borned on `1948-03-31` should be 64, but `DATEDIFF` will return 65 – Lamak Jan 22 '13 at 18:22
@Lamak- thanks. I dint know much about DATEDIFF – Abhishek kumar Jan 22 '13 at 18:25

score 0 · Answer 4 · answered Jan 22 '13 at 18:20

Try the Below Query... it will work perfectly...

   DECLARE @CalDate datetime;
   DECLARE @CurDate datetime;
   Declare @count INT   

   SET @CalDate ='03/31/2013'
   SET @count =0
   SELECT @CurDate = DateAdd(Month, 1, @CalDate)

   SET @count = @count + datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(@CurDate) as varchar)+'-'+cast(month(@CurDate) as varchar)+'-01' as datetime))))      

   SELECT ip_master.ssn, ip_master.firstname, ip_master.surname, ip_master.status,DATEDIFF (yy, DOB, cast(@CalDate as DateTime) + @count) - (        
    CASE SIGN (MONTH (DOB) - MONTH (cast(@CalDate as DateTime) + @count))        
     WHEN 1 THEN        
      1        
     WHEN -1 THEN         
      0         
     WHEN 0 THEN         
      CASE SIGN (DAY (DOB) - DAY (@CalDate + @count))        
       WHEN 1 THEN         
        1        
       WHEN 0 THEN        
        1       
       ELSE        
        0        
      END        
    END) as AGE into #temp
    FROM test_db.dbo.ip_master ip_master 

select * from #temp where  AGE >= 65

All that extra code **AND** an unnecessary temp table??? No, I"m afraid not. — Ken White, Jan 22 '13 at 18:22
@Ken White for testing purpose i have used Temp tables... and there is no extra coding... it's working for me... i have used this for many reports... — Pandian, Jan 22 '13 at 18:25

Age Calculation Query

4 Answers4

Linked