SQL 2008: Cast datetime as float(2) is rounding???

Was using this RoundTime function on a SQL 2000 box for the longest time, and it worked well. I use it to round datetime fields to the nearest (in this case) 5 minutes.

When I brought it over to SQL 2008, I was getting weird results. For example, rounding '12:25' to the nearest 5 would give me '12:20'  Makes no sense.

I've tracked it down to the following line:
select Cast(cast('10-10-2008 12:25' as datetime) as float(2))

On 2000 it returns: 39729.5173611111
On 2008 it returns: 39729.52

Why the sudden rounding?



Original function on SQL 2000:
 
FUNCTION [dbo].[udf_RoundTime] (@TimeIn datetime, @NearestN AS float(2))
Returns smalldatetime
As
 
 
    Begin
    DECLARE @Factor float(2)
    DECLARE @OutDT smalldatetime
    declare @timein datetime
    declare @nearestN float(2)
 
    SET @Factor = (1440/@NearestN)
    	SELECT @OutDT = Cast((ROUND(Cast(@TimeIn as float(2))*@Factor,0)/@Factor) as smalldatetime)
    	RETURN @OutDT
End

Open in new window

LVL 4
gozolietAsked:
Who is Participating?
 
gozolietConnect With a Mentor Author Commented:
Solved it myself. Remove the (2) (thought I tried that) and the rounding goes away.
Not sure why behavior is different in 2000 as 2008.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.