refer this blog post

SQL SERVER – Function to Round Up Time to Nearest Minutes Interval[^]

14,770,048 members

See more:

Good Day

I am trying to round time to the highest of the value specified

If my time is 10:05 if i round of the highest minutes interval of 5 it should be 10:05 only but it is giving me

10:10 which is false

I am trying to round time to the highest of the value specified

If my time is 10:05 if i round of the highest minutes interval of 5 it should be 10:05 only but it is giving me

10:10 which is false

```
DECLARE @Time AS TIME='10:05'
DECLARE @RoundToMin AS INT=5;
DECLARE @Hours AS INT;
SET @Hours =(SELECT Datepart(hh, @Time))
DECLARE @MIN AS INT
DECLARE @FinalTime AS DATETIME;
SET @MIN = Datepart(n, @Time)
IF CONVERT(INT, CONVERT(VARCHAR(90), ( ( @MIN / @RoundToMin ) * @RoundToMin ) + @RoundToMin)) >= 60
BEGIN
SET @Hours =@Hours + 1;
SET @FinalTime= CONVERT(TIME(7), CONVERT(VARCHAR(90), @Hours) + ':'
+ CONVERT(VARCHAR(90), 00));
END
ELSE
BEGIN
SET @FinalTime= CONVERT(TIME(7), CONVERT(VARCHAR(90), @Hours) + ':'
+ CONVERT(VARCHAR(90), ((@MIN / @RoundToMin) * @RoundToMin) + @RoundToMin));
END
SELECT @FinalTime
```

[EDIT - OP comment to non-solution]Quote:Function to Round Up Time to Highest Minutes Interval

if the time is 10:05 if i round of time highest of 5 minutes interval then it should be 10:05

but it giving me 10:10 :)

Comments

It would be interesting to see what the OPs expected results for 10:06 would be :-)

If you are expecting your result to be *next* number divisible by 5.

I would probably go about it differently - first check if the time is already one you want - i.e. if you divide by 5 is there a remainder. If not then calculate how many minutes need to be added - again by using the remainder when dividing by 5, and just add those minutes using a standard SQL function

For example:

This will return

However, watch what happens if you put in @Time = 23:59... you get

If you do want to determine that you've gone over a day threshold, then change the declaration of

`10:05`

then you actually only want times that are divisible by 5 - you are currently always rounding up to the I would probably go about it differently - first check if the time is already one you want - i.e. if you divide by 5 is there a remainder. If not then calculate how many minutes need to be added - again by using the remainder when dividing by 5, and just add those minutes using a standard SQL function

`DATEADD()`

For example:

```
DECLARE @Time AS TIME='10:05'
DECLARE @RoundToMin AS INT=5
DECLARE @MIN AS INT
DECLARE @FinalTime AS DATETIME
SET @MIN = Datepart(n, @Time)
IF (@MIN % @RoundToMin) > 0
SET @FinalTime = DATEADD(MINUTE, @RoundToMin - (@MIN % @RoundToMin), @Time)
else
SET @FinalTime = @Time
SELECT @FinalTime
```

This will return

`January, 01 1900 10:05`

for your input of 10:05 and `January, 01 1900 10:10`

for an input of 10:06However, watch what happens if you put in @Time = 23:59... you get

`January, 01 1900 00:00:00+0000`

... is that correct or do you want it to clock over to the next "day"?If you do want to determine that you've gone over a day threshold, then change the declaration of

`@Time`

to DECLARE @Time AS DATEwhich will returnTIME='23:59'

`January, `**02** 1900 00:00:00+0000

Comments

Thank you for replying :)

My pleasure. Quick question - what would you expect the results to be if the time was 10:06 ... 10:05 or 10:10?

10:06=10:10

10:05=10:05

:)

10:05=10:05

:)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject,
20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8
+1 (416) 849-8900