[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2766
  • Last Modified:

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

0
gozoliet
Asked:
gozoliet
1 Solution
 
gozolietAuthor 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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now