How Do I Round Off To Highest And Lowest Time

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

SQL

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 :)

Posted 8-Jul-14 22:27pm

surajemo

Updated 9-Jul-14 0:09am

CHill60

v3

Add a Solution

2 solutions

Solution 3

refer this blog post
SQL SERVER – Function to Round Up Time to Nearest Minutes Interval[^]

Posted 9-Jul-14 0:14am

DamithSL

Comments

CHill60 9-Jul-14 7:10am

Good link, which I did consider posting - but the Title of OP's post contradicts the body of his post somewhat "I am trying to round time to the highest of the value specified"
It would be interesting to see what the OPs expected results for 10:06 would be :-)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2014-07-09T01:04:00

If you are expecting your result to be 10:05 then you actually only want times that are divisible by 5 - you are currently always rounding up to the 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 DATEADD()

For example:

SQL

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:06

However, 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 DATETIME='23:59'

which will return January, 02 1900 00:00:00+0000