calculate ages in sql server?

[siamandm]

Hello Again :)
i have seen a tutorial online which showing how to calculate ages using sql server,

there is something which i can not get it .. is the in the example code below there is condition between MOnth and day, and as my knowledge when there is "AND" between two conditions , the two conditions must be applied
but in this code blow , the second part which says the satart date > current day is not applied which means it should subtract 0 not one

Code: Select all

Declare @StartDate date
set @StartDate='12/5/2016'

begin
select DATEDIFF(year,@StartDate,GETDATE()) - 

CASe
when
-- check the month and day
(MONTH(@startDate)> MONTH(GetDate()) )or (MONTH(@startDate)= MONTH(GetDate())
and

-- in this example for today today is 21 which is greater than start date day 
day(@startDate) > day(GetDate()))

then 1 else 0
end

End

i hope you got my points

[HansV]

DATEDIFF(year,@StartDate,GETDATE()) subtracts the year of StartDate from the current year, so in your example, it calculates 2017 - 2016 = 1.
DATEDIFF does not take into account whether StartDate is later in the year than the current date. To compensate for this, we subtract 1 from this difference if either the month of StartDate is later than the current month, or if the month of StartDate is equal to the current month AND the day of the month of StartDate is later than the current day of the month.
Perhaps it's a bit clearer if you omit the comments and the superfluous parentheses:

declare @startDate date
set @startDate = '12/5/2016'
declare @curDate date
set @curDate = GetDate()

begin
select DateDiff(year,@startDate,@curDate) -
case
when Month(@startDate) > Month(@curDate) or Month(@startDate) = Month(@curDate) and Day(@startDate) > Day(@curDate)
then 1 else 0
end

[siamandm]

thanks for your quick reply
the part which its not clear for me is the AND condition part this And is as below
First Condition OR (second condition AND third condition)
(First Condition or second condition) AND Third Condition

?

regards

[HansV]

AND has precedence above OR, so

a OR b AND c

is equivalent to

a OR (b AND c)

In the T-SQL statement above:

Month(@startDate) > Month(@curDate) or Month(@startDate) = Month(@curDate) and Day(@startDate) > Day(@curDate)

is equivalent to

Month(@startDate) > Month(@curDate) or (Month(@startDate) = Month(@curDate) and Day(@startDate) > Day(@curDate))

[siamandm]

thanks alot for your clarification, each time i ask something , i learn another things as well.

thanks alot