• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2776
  • 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
    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

Open in new window

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

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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