Datediff -Year and Rounding

Hi, I have a statement similar to this...
SELECT
DATEDIFF(year, acc.acct_anuit_birth_dt, GETDATE()) AS 'age'
FROM rpsacct_t acc
The acc.acct_anuit_birth_dt field is a date field. The statement returns
whole numbers based like 64 etc... What I would like it to do is to return
the age to two decimal places eg... if someone is 64 years and 6 months, I'd
like to get back 64.50 Any idea how I might do this ?
ThanksTry this:
SELECT
DATEDIFF(month, acc.acct_anuit_birth_dt, GETDATE())/12. AS age
FROM rpsacct_t acc
If this is not what you want, post more sample data and expected
results.
Razvan

DateDiff calculating ages incorrectly.

Hi,
I am trying to use datediff to calculate a persons age, based on their date
of brith. I am using the following function:
(datediff(year,[DOB],getdate()))
The formula calculates the ages correctly for people whose birthday falls on
a day and month before today (getdate()), but for those with a birthday afte
r
today it adds an extra year on.
Anyone got any suggestions about how to correctly calculate ages using a
date of birth?Actually, DateDiff, just counts the number of <DateInterval> "boundaries"
exist between the two dates... So from 1 Jan 2004 to 31 Dec 2005 is the same
as between 31 Dec 2004 and 1 Jan 2005, There's one Year Boundary between
bothe sets of dates...
To calculate Age, use the following:
Year(@.D2) - Year(@.D1)
- Case When Month(@.D2) > Month(@.D1) Or
(Month(@.D2)= Month(@.D1) And Day(@.D2) < Day(@.D1)) Then 1
Else 0 End
You could put this in UDF...
Create FUNCTION dbo.Age (@.DOB DateTime, @.CurDT DateTime)
RETURNS TinyInt
As
Begin
Declare @.Age SmallInt
Set @.Age = 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
Return @.Age
End
"Enterprise Andy" wrote:

> Hi,
> I am trying to use datediff to calculate a persons age, based on their dat
e
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
on
> a day and month before today (getdate()), but for those with a birthday af
ter
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?|||http://groups.google.ca/group/micro...49c2e
c8
AMB
"Enterprise Andy" wrote:

> Hi,
> I am trying to use datediff to calculate a persons age, based on their dat
e
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
on
> a day and month before today (getdate()), but for those with a birthday af
ter
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?|||Hi Andy,
"Enterprise Andy" <EnterpriseAndy@.discussions.microsoft.com> wrote in
message news:10C0C6CD-965D-4608-A47E-E720ACE40DDE@.microsoft.com...
> Hi,
> I am trying to use datediff to calculate a persons age, based on their
> date
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
> on
> a day and month before today (getdate()), but for those with a birthday
> after
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?
Try this:
CREATE FUNCTION uf_YearsDifference (@.initialDate DATETIME,
@.finalDateDATETIME)
RETURNS INT
AS
BEGIN
RETURN(
SELECT CASE WHEN
DATEADD(YEAR, DATEDIFF(YEAR, @.initialDate, @.finalDate), @.initialDate) >
@.finalDate
THEN DATEDIFF(YEAR, @.initialDate, @.finalDate) - 1
ELSE DATEDIFF(YEAR, @.initialDate, @.finalDate)
END
)
END
Andrea - www.absistemi.it|||Many thanks. Saved me a lot of trouble!!!
"CBretana" wrote:
> Actually, DateDiff, just counts the number of <DateInterval> "boundaries"
> exist between the two dates... So from 1 Jan 2004 to 31 Dec 2005 is the sa
me
> as between 31 Dec 2004 and 1 Jan 2005, There's one Year Boundary between
> bothe sets of dates...
> To calculate Age, use the following:
> Year(@.D2) - Year(@.D1)
> - Case When Month(@.D2) > Month(@.D1) Or
> (Month(@.D2)= Month(@.D1) And Day(@.D2) < Day(@.D1)) Then 1
> Else 0 End
> You could put this in UDF...
> Create FUNCTION dbo.Age (@.DOB DateTime, @.CurDT DateTime)
> RETURNS TinyInt
> As
> Begin
> Declare @.Age SmallInt
> Set @.Age = 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
> Return @.Age
> End
>
> "Enterprise Andy" wrote:
>