SmallDate formula result error

[Schnappa]

Hi all

Having a problem with a Datediff formula, where I am getting a whole number back (which is rounding someone 17.78 yrs old to 18).

Currently the formula is

(datediff(month,RM_ACCT.birth_dt,getdate())/12) AS Age

With birthdate as smalldate and Age as numeric with precision 18 and scale 2.

I have tried many changes to the data type and a bunch of formulas I have found on this webiste, to no avail. I still keep getting rounded up numbers.

I really need to get the two decimal places, and am having complete brain freeze. Many thanks if you can help

Cheers

GV

 

[Jamfool]

create function udfCalcAge(@DateOfBirth smalldatetime,@EndDate smalldatetime)
returns numeric(18,2)
as
begin
Declare @i integer,@j numeric(18,2)
--Example Use:
--SELECT [dbo].[udfCalcAge] ('2006-05-25',getdate())

--Adjust for boundaries
if(datepart(dy,@DateOfBirth)>datepart(dy,@EndDate))
begin
set @i = -1
end
else
begin
set @i =0
end

--YearPart
set @i = datediff(yy,@DateOfBirth,@EndDate)+ @i

--Assume that the decimal part is days
set @j = @i + datediff(dd,dateadd(yy,@i,@DateOfBirth),@EndDate)/365.00

return @j
end
go

Note that this doesnt deal with leaps, and the chosen precision of 2 decimal places is not really accurate enough.

 

[SQLSister]

You are running into integer math.

this is what worked for me to get decimal places

declare @bdate as smalldatetime
set @bdate = '05-30-1972'

select (datediff(month,@bdate,getdate())/12.0)

Questions about posting. See faq183-874