Rounding datetime to nearest fraction of a day using TSQL

Question

I have to group a datetime field to a fraction of a day using TSQL. For example to the nearest half second. Half a second is 1/172800 of a day. Using 172800 as multiplier in following function should do the trick:

CREATE FUNCTION [dbo].[RoundTime] (@Time datetime, @Multiplier float) RETURNS datetime
AS
BEGIN
   DECLARE @RoundedTime datetime
   SET @RoundedTime= ROUND(convert(float, @Time) * @Multiplier, 0) / @Multiplier
   RETURN @RoundedTime
END

But if i run this statement: SELECT RoundTime('2012-06-27 10:00:04.397',172800) the result is 2012-06-27 10:00:04.497

It should have been 2012-06-27 10:00:04.500

Digging deeper I tried to convert the fraction to a datetime using following statement:

select convert(datetime,1/172800.0)

The result was this:

1900-01-01 00:00:00.497

It should have been: 1900-01-01 00:00:00.500

I sure could do with a little help on this one. How do I do this?

Answer 1

Basically, you can't. It's due to accuracy of the DATETIME data type: Rounded to increments of .000, .003, or .007 seconds.

Answer 2

select 
	getdate(),
	case when datepart(ms,getdate())%100 < 50 then dateadd(ms, - datepart(ms,getdate())%100 ,getdate())
	else dateadd(ms, 100-datepart(ms,getdate())%100 ,getdate())
	end
// OR -- depending on your need
select getdate(),
	case 
			when datepart(ms,getdate()) < 500 then dateadd(ms, -datepart(ms,getdate()),getdate())
			else dateadd(ms, -datepart(ms,getdate()) + 1000 ,getdate())
			end

replace the getdate() with your datetime, i guess this is you need.

regards

joon

Answer 3

DECLARE	@Sample TABLE
	(
		dt DATETIME NOT NULL,
		Correct DATETIME NOT NULL
	);

INSERT	@Sample
	(
		dt,
		Correct
	)
VALUES	('20120627 10:00:04.397', '20120627 10:00:04.500'),
	('20120627 10:00:04.097', '20120627 10:00:04.000'),
	('20120627 10:00:04.797', '20120627 10:00:05.000');

-- Solution by SwePeso
SELECT	dt,
	Correct,
	DATEADD(MILLISECOND, 60000 * DATEPART(MINUTE, dt) + 1000 * DATEPART(SECOND, dt) + (DATEPART(MILLISECOND, dt) + 250) / 500 * 500, DATEADD(HOUR, DATEDIFF(HOUR, '19000101', dt), '19000101')) AS SwePeso
FROM	@Sample

---

N 56°04'39.26"
E 12°55'05.63"

Answer 4

ALTER FUNCTION dbo.RoundTime
(
	@Time DATETIME,
	@Multiplier INT
)
RETURNS DATETIME
AS
BEGIN
	RETURN	(
			SELECT	DATEADD(MILLISECOND, (theFraction + theYak / 2) / theYak * theYak, theDate)
				FROM	(
						SELECT	DATEADD(DAY, DATEDIFF(DAY, '17530101', @Time), '17530101') AS theDate,
							3600000 * DATEPART(HOUR, @Time) + 60000 * DATEPART(MINUTE, @Time) + 1000 * DATEPART(SECOND, @Time) + DATEPART(MILLISECOND, @Time) AS theFraction,
							86400000 / @Multiplier AS theYak
					) AS d
		)
END
GO
DECLARE	@Sample TABLE
	(
		dt DATETIME NOT NULL,
		Correct DATETIME NOT NULL
	);

INSERT	@Sample
	(
		dt,
		Correct
	)
VALUES	('20120627 10:00:04.397', '20120627 10:00:04.500'),
	('20120627 10:00:04.097', '20120627 10:00:04.000'),
	('20120627 10:00:04.797', '20120627 10:00:05.000');

SELECT	dt,
	Correct,
	dbo.RoundTime(dt, 172800)
FROM	@Sample;

---

N 56°04'39.26"
E 12°55'05.63"

Answer 5

Stefan Hoffmann is right DateTime and the function GETDATE() that returns a DateTime result, have a precision of more or less 3 ms. MS SQL Server and Sybase SQL Server have the same roots. The Sybase SQL Server used to have a hartbeat of 3 ms. Therefore, only the results .000, .003, or .007 seconds will show up. If you have a high enough version of MS SQL Server, use the DateTime2 datatype instead. DateTime2 has 7 decimals. If you want to have ms precission, use DateTime2(3). The (3) part signifies the precision you want. Use SYSDATETIME() instead of GETDATE() instead, since SYSDATETIME() returns a DateTime2(7) result.

Answer 6

Thanks everyone.

Stefen is right, but there's nothing like workarounds.

Joon84 and SwePeso put me on track. I just got my solution before SwePeso's last answer.

Following function round to nearest fraction of a day using the multiplier. Because of the accuracy error I then round to neatest 1/10 of a millisecond.

alter FUNCTION [dbo].[RoundTime] (@Time datetime, @Multiplier float) RETURNS datetime
AS
BEGIN
    DECLARE @RoundedTime datetime
Declare @MS as int
    SET @RoundedTime= ROUND(convert(float, @Time) * @Multiplier, 0) / @Multiplier
    set @MS = datepart(ms,@RoundedTime)
if @MS > 0 
begin 
SET @RoundedTime= dateadd(ms, -@MS ,@RoundedTime)
SET @RoundedTime= dateadd(ms, round(@MS,-1) ,@RoundedTime)
end
    RETURN @RoundedTime
END

Answer 7

Peter, why you are always right? :)

---

 | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

Answer 8

Because I'm good?

;-)

---

N 56°04'39.26"
E 12°55'05.63"