CODEWITHSUNDEEP
t-sqlrounding
Vitor Silva
Vitor Silva

Reputation: 17612

How to Round a Time in T-SQL

I'me looking for a function that would receive a time and would round it to the next/previous hour / half-hour / quarter / minute.

Upvotes: 18

Views: 23138

Answers (2)

IAdapter
IAdapter

Reputation: 64707

Try this function

CREATE FUNCTION [dbo].[RoundTime] (@Time datetime, @RoundTo float)
RETURNS datetime
AS
BEGIN
   DECLARE @RoundedTime smalldatetime
   DECLARE @Multiplier float

   SET @Multiplier= 24.0/@RoundTo

   SET @RoundedTime= ROUND(CAST(CAST(CONVERT(varchar,@Time,121) AS datetime) AS float) * @Multiplier,0)/@Multiplier

   RETURN @RoundedTime
END



select dbo.roundtime('13:15',0.5)

The 1st param is the time to be rounded and the 2nd will be base on your list (0.5-half hour, 1-one hour, ...)

Upvotes: 30

Kevin
Kevin

Reputation: 61

Very nice thanks. I used it in-line to round to 15 minutes

convert(smalldatetime,ROUND(cast(TDatalog.Time as float) * (24/.25),0)/(24/.25)) AS RoundedTime

Upvotes: 6

Related Questions