Post reply

How to get difference between two dates in days and hours?

  • April 7, 2013 at 1:49 pm

    #270675

    Hi, I'm looking code to find difference between two dates in days and hours.

    Example:

    date1: 2013-04-07 14:45:41.013 - date2: 2013-04-05 10:45:41.013

    I need output like 2 days and 6 hours?

  • April 7, 2013 at 2:05 pm

    #1604268

    Use the DATEDIFF functions ...

    for example

    SELECT DATEDIFF ( datepart , startdate , enddate )

    This will return the number of days difference.

    SELECT DATEDIFF(d,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')

    returns 2 days

    This returns the total number of hours difference (52)

    SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')

    --combining every thing I think will give you what you are looking for:

    SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')/24 AS 'Days'

    ,DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013') -

    (DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')/24)*24 AS 'Hours'

    Result:

    DaysHours

    24

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • April 7, 2013 at 9:53 pm

    #1604273

    Thanks, It's worked.

  • March 13, 2018 at 8:00 am

    #1982930

    bitbucket-25253 - Sunday, April 7, 2013 2:05 PM

    Use the DATEDIFF functions ... for example SELECT DATEDIFF ( datepart , startdate , enddate )This will return the number of days difference.SELECT DATEDIFF(d,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')returns 2 daysThis returns the total number of hours difference (52)SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')--combining every thing I think will give you what you are looking for:SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')/24 AS 'Days' ,DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013') - (DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')/24)*24 AS 'Hours'Result:DaysHours24

    This will only works, when minutes and seconds are same of both the dates, but if we change minutes then values are not correct.
    Like:  SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:00:41.013')
    I have changed the minutes in this query,
    After executing this query we still get the same output i.e. 4 hours
    But According to me output should be 3 hours,
    Can you tell me how we can get 3 hours output.
    Please help

    Thanks
    Deepak Sharma

  • March 13, 2018 at 8:24 am

    #1982942

    Since the date you are comparing has a precision at the microsecond level you would need to use the precision you are using to determine the difference between dates..

    SELECT DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015') /*Difference in Micro seconds between two dates*/
                 ,DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015')/1000/60/60 as hrs_diff /*Convert the difference in Micro Seconds as Hours*/
                 ,(DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015')/1000/60) % 60 as min_diff /*Gets the residual minutes*/

  • March 13, 2018 at 12:35 pm

    #1983038

    dsharmadbec - Tuesday, March 13, 2018 8:00 AM

    bitbucket-25253 - Sunday, April 7, 2013 2:05 PM

    Use the DATEDIFF functions ... for example SELECT DATEDIFF ( datepart , startdate , enddate )This will return the number of days difference.SELECT DATEDIFF(d,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')returns 2 daysThis returns the total number of hours difference (52)SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')--combining every thing I think will give you what you are looking for:SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')/24 AS 'Days' ,DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013') - (DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')/24)*24 AS 'Hours'Result:DaysHours24

    This will only works, when minutes and seconds are same of both the dates, but if we change minutes then values are not correct.
    Like:  SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:00:41.013')
    I have changed the minutes in this query,
    After executing this query we still get the same output i.e. 4 hours
    But According to me output should be 3 hours,
    Can you tell me how we can get 3 hours output.
    Please help

    Thanks
    Deepak Sharma

    You need to understand that DATEDIFF is counting the number of boundaries you are crossing based on the date type you are using, which in your case is hours (hh).

  • March 13, 2018 at 12:57 pm

    #1983042

    george-178499 - Tuesday, March 13, 2018 8:24 AM

    Since the date you are comparing has a precision at the microsecond level you would need to use the precision you are using to determine the difference between dates..

    SELECT DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015') /*Difference in Micro seconds between two dates*/
                 ,DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015')/1000/60/60 as hrs_diff /*Convert the difference in Micro Seconds as Hours*/
                 ,(DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015')/1000/60) % 60 as min_diff /*Gets the residual minutes*/

    Also, your DATEDIFF is choosing milliseconds, not microseconds.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • March 13, 2018 at 7:16 pm

    #1983076

    First, for all those that are about to remind us that this post was necro'd from a 5 year old post, we already know that.  Please drive through. 😉

    What the original post is looking for is a simple "interval" or "duration".  Please see the following article for a simple way to do this and modify the formatting section for however you see fit.

    Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)[/url]

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 13, 2018 at 7:49 pm

    #1983079

    Or use a function described in this article:

    http://www.sqlservercentral.com/articles/Datetime+conversions/153316/

    _____________
    Code for TallyGenerator

  • March 13, 2018 at 8:49 pm

    #1983083

    sgmunson - Tuesday, March 13, 2018 12:57 PM

    george-178499 - Tuesday, March 13, 2018 8:24 AM

    Since the date you are comparing has a precision at the microsecond level you would need to use the precision you are using to determine the difference between dates..

    SELECT DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015') /*Difference in Micro seconds between two dates*/
                 ,DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015')/1000/60/60 as hrs_diff /*Convert the difference in Micro Seconds as Hours*/
                 ,(DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015')/1000/60) % 60 as min_diff /*Gets the residual minutes*/

    Also, your DATEDIFF is choosing milliseconds, not microseconds.

    Oh yea :-). The precision is at the millisecond level. The output computed it at the milli second itself though.

  • March 13, 2018 at 9:42 pm

    #1983084

    george-178499 - Tuesday, March 13, 2018 8:49 PM

    sgmunson - Tuesday, March 13, 2018 12:57 PM

    george-178499 - Tuesday, March 13, 2018 8:24 AM

    Since the date you are comparing has a precision at the microsecond level you would need to use the precision you are using to determine the difference between dates..

    SELECT DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015') /*Difference in Micro seconds between two dates*/
                 ,DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015')/1000/60/60 as hrs_diff /*Convert the difference in Micro Seconds as Hours*/
                 ,(DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015')/1000/60) % 60 as min_diff /*Gets the residual minutes*/

    Also, your DATEDIFF is choosing milliseconds, not microseconds.

    Oh yea :-). The precision is at the millisecond level. The output computed it at the milli second itself though.

    "ms" is "millisecond", not microsecond (if that's what you meant).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 14, 2018 at 12:18 am

    #1983098

    Jeff Moden - Tuesday, March 13, 2018 9:42 PM

    george-178499 - Tuesday, March 13, 2018 8:49 PM

    sgmunson - Tuesday, March 13, 2018 12:57 PM

    george-178499 - Tuesday, March 13, 2018 8:24 AM

    Since the date you are comparing has a precision at the microsecond level you would need to use the precision you are using to determine the difference between dates..

    SELECT DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015') /*Difference in Micro seconds between two dates*/
                 ,DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015')/1000/60/60 as hrs_diff /*Convert the difference in Micro Seconds as Hours*/
                 ,(DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015')/1000/60) % 60 as min_diff /*Gets the residual minutes*/

    Also, your DATEDIFF is choosing milliseconds, not microseconds.

    Oh yea :-). The precision is at the millisecond level. The output computed it at the milli second itself though.

    "ms" is "millisecond", not microsecond (if that's what you meant).

    Yes. I was meaning milliseconds, Although what I wrote in the post and also in the comment portion was saying 
    DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015') /*Difference in Micro seconds between two dates*/
    I was computing the differences in milliseconds

Viewing 12 posts - 1 through 11 (of 11 total)

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