Post reply

Difference in seconds to round to minute

  • May 20, 2014 at 1:32 pm

    #285674

    Hi, I'm trying to get the difference between the two times in minutes with the seconds rounded up or down

    As an example the difference in the times:

    1900-01-01 09:27:49.000, 1900-01-01 09:32:28.000 is 279 seconds which is 4.65 minutes,

    using

    select

    cast(round(datediff(second, convert(datetime, '1900-01-01 09:27:49.000'), convert(datetime, '1900-01-01 09:32:28.000')) / 60, 2) as numeric(18,2))

    I am receiving the value 4.00 instead of 5 which I would like 4.65 to round to. How can I get the difference in seconds to round to the minute ?

  • May 20, 2014 at 1:42 pm

    #1715120

    The problem is that you're dividing 2 integers. That way, the decimal part is truncated. You need to cast any of the 2 integers into a decimal. And you should use CEILING to round up everytime.

    select CEILING(CAST(datediff(second, '1900-01-01 09:27:49.000', '1900-01-01 09:32:28.000') as numeric(18,2)) / 60)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • May 20, 2014 at 1:49 pm

    #1715123

    Marcus Farrugia (5/20/2014)

    Hi, I'm trying to get the difference between the two times in minutes with the seconds rounded up or down

    As an example the difference in the times:

    1900-01-01 09:27:49.000, 1900-01-01 09:32:28.000 is 279 seconds which is 4.65 minutes,

    using

    select

    cast(round(datediff(second, convert(datetime, '1900-01-01 09:27:49.000'), convert(datetime, '1900-01-01 09:32:28.000')) / 60, 2) as numeric(18,2))

    I am receiving the value 4.00 instead of 5 which I would like 4.65 to round to. How can I get the difference in seconds to round to the minute ?

    Elementary my dear Watson (fictitious Holmes quote), there is an implied type directive where the results of datediff is divided by an intiger (60). If 60 is replaced by 60.0, the result will be different (4.65). And of course it will never be 5 as it is rounded to two decimal places:w00t:

    Change the code to

    select

    cast(round(datediff(second, convert(datetime, '1900-01-01 09:27:49.000'), convert(datetime, '1900-01-01 09:32:28.000')) / 60.0, 0) as numeric(18,2)) to get the desired results

    😎

  • May 20, 2014 at 2:05 pm

    #1715131

    Thank you to both, I selected Luis as the answer because of the direction to the Ceiling function, was a learning curve for me.

  • May 20, 2014 at 2:07 pm

    #1715135

    Thank you for the feedback. If I'm not mistaken, you could select both. 😀

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • May 20, 2014 at 2:07 pm

    #1715136

    Marcus Farrugia (5/20/2014)

    Thank you to both, I selected Luis as the answer because of the direction to the Ceiling function, was a learning curve for me.

    Good stuff!

    😎

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply