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))
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)