Calculating Age...

Hello..
I am attempting to write a T-SQL script that will calculate a person's age
based on a date in the DOB field. The DOB field is a smalldatetime. I want
to return this calculated field in a result set. So for example, if the
date in the DOB field is 01/01/1900... I want the age to be returned as 105
(2005 - 1900). I am using SQL Server 2000
Any help would be appreciated...
Thanks,
Bretthttp://www.aspfaq.com/2233
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Brett Davis" <bdavis123@.cox.net> wrote in message
news:OTREukdQFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hello..
> I am attempting to write a T-SQL script that will calculate a person's age
> based on a date in the DOB field. The DOB field is a smalldatetime. I
> want to return this calculated field in a result set. So for example, if
> the date in the DOB field is 01/01/1900... I want the age to be returned
> as 105 (2005 - 1900). I am using SQL Server 2000
> Any help would be appreciated...
> Thanks,
> Brett
>
>|||So say that this date is the birth date:
DROP FUNCTION TIMEMEMORIAL
GO
CREATE FUNCTION TIMEMEMORIAL
(
@.BIRTHDATE smalldatetime,
@.CURRENTDATE smalldatetime
)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @.CURRENTYEAR CHAR(4)
DECLARE @.BIRTHYEAR CHAR(4)
DECLARE @.Result varchar(200)
SET @.BIRTHYEAR = CAST(YEAR(@.BIRTHDATE) as Char(4))
SET @.CURRENTYEAR = CAST(YEAR(@.CURRENTDATE) as Char(4))
Select @.result = CAST(datediff(yy,@.BIRTHDATE,@.CURRENTDATE
) AS varchar(10)) +
' (' + @.BIRTHYEAR + ' - ' + @.CURRENTYEAR + ')'
RETURN @.Result
END
Select dbo.TimeMemorial('20000101',getdate())
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Brett Davis" <bdavis123@.cox.net> schrieb im Newsbeitrag
news:OTREukdQFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hello..
> I am attempting to write a T-SQL script that will calculate a person's age
> based on a date in the DOB field. The DOB field is a smalldatetime. I
> want to return this calculated field in a result set. So for example, if
> the date in the DOB field is 01/01/1900... I want the age to be returned
> as 105 (2005 - 1900). I am using SQL Server 2000
> Any help would be appreciated...
> Thanks,
> Brett
>
>|||See :
http://groups.google.ca/groups?selm...FTNGP09.phx.gbl
Anith|||I think this is easier;
checking for VALID data...
Making assumption that your want it from TODAY
select
case
when ISDATE(convert(char(15),[DATEFIELD])) = 0 then null
else
datediff(year, convert(datetime,([DATEFIELD])),getdate(
))
end
"Brett Davis" wrote:

> Hello..
> I am attempting to write a T-SQL script that will calculate a person's age
> based on a date in the DOB field. The DOB field is a smalldatetime. I wa
nt
> to return this calculated field in a result set. So for example, if the
> date in the DOB field is 01/01/1900... I want the age to be returned as 10
5
> (2005 - 1900). I am using SQL Server 2000
> Any help would be appreciated...
> Thanks,
> Brett
>
>
>|||On Fri, 15 Apr 2005 10:29:04 -0700, Jim Yurt wrote:

>I think this is easier;
>checking for VALID data...
>Making assumption that your want it from TODAY
>select
>case
>when ISDATE(convert(char(15),[DATEFIELD])) = 0 then null
>else
>datediff(year, convert(datetime,([DATEFIELD])),getdate(
))
>
>end
Hi Jim,
But that will return the number of year boundaries (dec 31 / Jan 1)
since the birth date, not the age!
SELECT DATEDIFF(year, '20041231', '20050101'),
DATEDIFF(year, '20040101', '20051231')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try This:
If @.DOB is datetime date of birth, and
@.CurDT is a dateTime with current date, then
Select Year(@.CurDT) - Year(@.DOB) -
Case When Month(@.CurDT) < Month(@.DOB) Then 1
When Month(@.CurDT) > Month(@.DOB) Then 0
When Day(@.CurDT) < Day(@.DOB) Then 1
Else 0 End
"Brett Davis" wrote:

> Hello..
> I am attempting to write a T-SQL script that will calculate a person's age
> based on a date in the DOB field. The DOB field is a smalldatetime. I wa
nt
> to return this calculated field in a result set. So for example, if the
> date in the DOB field is 01/01/1900... I want the age to be returned as 10
5
> (2005 - 1900). I am using SQL Server 2000
> Any help would be appreciated...
> Thanks,
> Brett
>
>
>