Case Statement with Datediff and DatePart

Question

Can some one help with a case statement please, what I need is the query to show is the following. I know there are ways to do this easier but I just need help on the Case Statement.

--If the Current Month is ‘Less Than’ the DOB Month, then take ‘1’ of the Total Years to give me 41. --If the Current Month is ‘Greater Than’ the DOB Month then the Age is Correct. --However if the Current Month is ‘Equal’ to the DOB Month then we need to go to Day level to get the correct Age.

Set @DOB = '01 November 1971'
Set @Today = GETDATE()


SELECT Datediff(Year,@DOB,@Today) AS Years, 
Datepart(Month,@DOB) As DOB_Month, 
Datepart(Day, @DOB) as DOB_Day,
DatePart(Month, @Today) As Current_Month, 
Datepart(Day,@Today) AS Current_Day

How about [this](http://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate) and [this](http://stackoverflow.com/questions/2900217/getting-age-in-years-in-a-sql-query)? — Iswanto San, Mar 04 '13 at 09:08
Like I said I have been asked to use a Case Statement; Something like this CASE WHEN DatePart(Month, @Today) < Datepart(Month,@DOB) THEN Datediff(Year,@DOB,@Today) THEN -1 ELSE DatePart(Month, @Today) = Datepart(Month,@DOB) THEN Datepart(Day,@Today) < Datepart(Day, @DOB) THEN Datediff(Year,@DOB,@Today) THEN -1 ELSE END — wafw1971, Mar 04 '13 at 09:10

score 3 · Accepted Answer · answered Mar 04 '13 at 09:21

Try this :

DECLARE @DOB DATE= '01 November 1971'
DECLARE @TODAY DATE = GETDATE()

SELECT CASE 
WHEN DATEPART(MONTH, @TODAY) < DATEPART(MONTH,@DOB) THEN DATEDIFF(YEAR,@DOB,@TODAY) - 1
WHEN DATEPART(MONTH, @TODAY) > DATEPART(MONTH,@DOB) THEN DATEDIFF(YEAR,@DOB,@TODAY)
ELSE
    CASE 
         WHEN DATEPART(DAY, @TODAY) < DATEPART(DAY,@DOB) THEN DATEDIFF(YEAR,@DOB,@TODAY) - 1
         ELSE DATEDIFF(YEAR,@DOB,@TODAY)  
    END       
END

Sachin · Answer 2 · 2013-03-04T09:28:08.260

You can try this :

case 
    when DatePart(Month, @Today)  > Datepart(Month,@DOB) then Datediff(Year,@DOB,@Today) 
    when DatePart(Month, @Today)  < Datepart(Month,@DOB) then (Datediff(Year,@DOB,@Today) - 1)
    when DatePart(Month, @Today)  = Datepart(Month,@DOB) then 
        case 
            when DatePart(Day, @Today)  >= Datepart(Day,@DOB) then (Datediff(Year,@DOB,@Today) )
            when DatePart(Day, @Today)  < Datepart(Day,@DOB) then (Datediff(Year,@DOB,@Today) - 1 )
        end
end as AgeCompleted,

t-clausen.dk · Answer 3 · 2013-03-04T14:26:31.257

0

declare @DOB date = '19680411'
select datediff(year, @DOB, getdate())- case when month(@DOB)*32 + day(@DOB) > 
month(getdate()) * 32 + day(getdate()) then 1 else 0 end

edited Mar 04 '13 at 14:26

answered Mar 04 '13 at 13:51

t-clausen.dk

43,517
12
59
92

Case Statement with Datediff and DatePart

3 Answers3

Linked