Using CASE clause to find age

Question

SELECT * FROM hr.Employees; 

SELECT firstname,
       lastname,   
       (CASE WHEN (month(getdate()) > month(birthdate))             
             THEN datediff(yy,birthdate,getdate())              
             ELSE datediff(yy,birthdate,getdate()) -1
        END) AS _years,          --years         
       (CASE WHEN (month(getdate()) > month(birthdate))               
             THEN month(getdate()) - month(birthdate)               
             ELSE month(getdate()) - month(birthdate) +12            
        END) AS _months,     --months
       (CASE WHEN (day(getdate()) > (day(birthdate)))               
             THEN day(getdate()) - day(birthdate)                
             ELSE month(getdate()) - month(birthdate) +11  &   day(getdate()) + (day(EOMonth(birthdate)) - day(birthdate))        
        END) AS _days          --days
FROM hr.Employees

Here is the code for finding age in yy,mm,dd. The problem I am having is in the days section.

I want to do is (if current day < birthdate day ) then (currentMonth-birthmonht)+11 and day(getdate()) + (day(EOMonth(birthdate)) - day(birthdate)).

This is very badly formatted and the SQL looks broken. – Menelaos Oct 18 '14 at 16:02 — Menelaos, Oct 18 '14 at 16:02

score 0 · Answer 1 · edited May 23 '17 at 10:25

I want to do is (if current day < birthdate day ) then (currentMonth-birthmonht)+11 and day(getdate()) + (day(EOMonth(birthdate)) - day(birthdate)).

This can't be done in the CASE in the days column you need to add a new WHEN clause in the CASE for month column itself. Like the following code

SELECT firstname,
       lastname,   
       (CASE WHEN (month(getdate()) > month(birthdate))  THEN datediff(yy,birthdate,getdate())              
             ELSE datediff(yy,birthdate,getdate()) -1
        END) AS years,                
       (CASE WHEN (month(getdate()) > month(birthdate))  THEN month(getdate()) - month(birthdate)
             WHEN (day(getdate()) > (day(birthdate))) THEN month(getdate()) - month(birthdate) +11 
             ELSE month(getdate()) - month(birthdate) +12  
        END) AS months,    
       (CASE WHEN (day(getdate()) > (day(birthdate)))    THEN day(getdate()) - day(birthdate)                
             ELSE day(getdate()) + (day(EOMonth(birthdate)) - day(birthdate))        
        END) AS days          
FROM Employees

Here is a SQL fiddle demo of the query: SQL Fiddle

If you can use T-SQL this answer shows how to do this in T-SQL.

Using CASE clause to find age

1 Answers1