AGE?

[jazerr]

I am using SQL 2000. Our product is a Student Information System, and so it is incredibly important that we specify the correct age for each kid...this is the formula I am using in a SP, but it won't display the correct age for any person if their birthday is later in the year than the current date...
age = CASE
WHEN (DATEPART(MONTH,ut_person.dateofbirth) = DATEPART(MONTH,GETDATE())) AND
(DATEPART(DAY,ut_person.dateofbirth) < DATEPART(DAY,GETDATE())) AND
(DATEDIFF(DAY,ut_person.dateofbirth,GETDATE()) < 8395)
THEN
CONVERT(varchar(3),DATEDIFF(YEAR,ut_person.dateofbirth,GETDATE()) - 1)

WHEN (DATEDIFF(DAY,ut_person.dateofbirth,GETDATE()) < 8395)
THEN
CONVERT(varchar(3),DATEDIFF(YEAR,ut_person.dateofbirth,GETDATE()))

WHEN (DATEDIFF(day,ut_person.dateofbirth,GETDATE()) > 8395)
THEN
'Over 22'

ELSE NULL
END

 

[Ruairi]

select "age" = DateDiff(mm,'Aug 15 1977', getdate()) / 12
[code] 

this simple querey will return the age of someone born on august 15 1977. just substitute your dateofbirth variable for the 'Aug 15 1977' string. It has to be done in months and divided by 12 to avoid misreporting the age if the birthday is later than the current date.  Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?  
For innovative, low cost solutions check out my website. 
[URL unfurl="true"]www.plccontroltechnologies.com[/URL]