Rounding datetime to nearest fraction of a day using TSQL

# Rounding datetime to nearest fraction of a day using TSQL

• 8. srpna 2012 11:02

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)

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?

### Všechny reakce

• 8. srpna 2012 11:23

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

```select 	getdate(),
case when datepart(ms,getdate())%100 < 50 then dateadd(ms, - datepart(ms,getdate())%100 ,getdate())
end// OR -- depending on your needselect 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

• 8. srpna 2012 11:36

```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"

• 8. srpna 2012 11:56

```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"

• 8. srpna 2012 12:00

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.
• 8. srpna 2012 12:26

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
end
RETURN @RoundedTime
END

• 8. srpna 2012 12:29

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!

• 9. srpna 2012 19:31

Because I'm good?

;-)

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