Solved

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

Posted on 2008-10-07
1
2,742 Views
Last Modified: 2008-10-07
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
Comment
Question by:gozoliet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 4

Accepted Solution

by:
gozoliet earned 0 total points
ID: 22658857
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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question