here's a way to calculate someone's age on a given date. also, you can also sort by birthday (month/day) excluding the year.
the query just performs a datediff, BUT adjusts by 0/1 given the time of year -- before or after the person's birthday.
i.e. you just need to adjust by "-1" if the date you're evaluating is prior to the birthday in that year.
calculate age :
DECLARE @DOB datetime, @later datetime
SELECT @DOB='07/1/1970', @later='07/1/2003'
Select DateDIFF( yy, @DOB, @later ) -
CASE
WHEN @later >= DateAdd( yy, DateDIFF(yy,@DOB,@later), @DOB )
THEN 0
ELSE 1
END
AS Age
here's the 'birthday' sort :
select * from customers
order by Datepart(mm, @dob), Datepart(dd, @dob)
here's another example, though not as slick as the one above, for calculating age (works on the same principle).
select datediff( yy, @dob, @later ) -
case
when 100*month(@dob) + day(@dob) >= 100*month(@later) + day(@later)
then 0
else 1
end as age