Solved

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

Posted on 2008-10-07
1
2,720 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - stored prodecure with execution plan make faster 24 115
IN with @variable 5 25
Upgrading SQL Server Management Tools 7 36
Sql query 107 22
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

948 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now