CODEWITHSUNDEEP
sqlsql-serversql-server-2012
SeanC
SeanC

Reputation: 15923

sql scalar function not returning correct value

I have written a scalar function to try to return a datetime that is rounded down to a specified multiple.

The function is based from this formula

select dateadd(minute, (datediff(minute, 0, '2014-01-05 12:23:05') / 30) * 30, 0)

that correctly returns

2014-01-05 12:00:00.000

I tried to write it as a function:

CREATE FUNCTION [dbo].[RoundTime] (@Time datetime, @RoundTo float) RETURNS datetime
AS
BEGIN
    DECLARE @RoundedTime datetime
    SET @RoundedTime = dateadd(minute, (datediff(minute, 0, @Time) / @RoundTo) * @RoundTo, 0) 
    RETURN @RoundedTime
END

but when I run a select statement 

select dbo.roundtime('2014-01-05 12:23:05',30)

or even when I force the time into a datetime format to match the definition

select businessusers.dbo.roundtime(cast('2014-01-05 12:23:05' as datetime),30)

it returns this:

2014-01-05 12:23:00.000

What am i doing wrong?

Upvotes: 0

Views: 834

Answers (1)

Andriy M
Andriy M

Reputation: 77717

This method of rounding relies on the division's being integral, as Lamak has correctly noted.

In SQL Server, division is integral when both operands are integers.

In your first example, the result of DATEDIFF is int by definition and the 30 is recognised by the parser as an int too. Therefore, the query works as expected.

In your function, however, one of the operands of the division, @RoundTo, is declared to be a float. That prescribes SQL Server to use normal division, preventing any rounding from happening.

There are basically two ways of resolving this. One is, obviously, to change the type of @RoundTo to an integer type. (Usually int works fine, even though its capacity may appear too large for this use case.)

The other method, if for some reason you want to stick with the float (e.g. you want to be able to round down to the nearest half-minute or some such interval), would be to apply the FLOOR() function to the division's result:

floor(datediff(minute, 0, @Time) / @RoundTo) * @RoundTo

However, if @RoundTo was not a whole number, the result of multiplication by @RoundTo might not be whole either. To make the resulting intervals consistent, you would probably need to convert the minutes to seconds at the final stage:

dateadd(second, floor(datediff(minute, 0, @Time) / @RoundTo) * @RoundTo * 60, 0)

Upvotes: 2

Related Questions