Click here to Skip to main content
14,770,048 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
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
Updated 9-Jul-14 1:09am
v3

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

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
   
Comments
surajemo 9-Jul-14 8:29am
   
Thank you for replying :)
CHill60 9-Jul-14 8:32am
   
My pleasure. Quick question - what would you expect the results to be if the time was 10:06 ... 10:05 or 10:10?
surajemo 14-Jul-14 9:17am
   
10:06=10:10
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