Find Hours, Minutes and Seconds in between two Datetime

In one of the previous articles, we explored how to find the difference between two dates.

In this short snippet, let us see how to find the Hours, Minutes and Seconds in between two Datetime.

-- Find Hours, Minutes and Seconds in between two datetime
DECLARE @First datetime
DECLARE @Second datetime
SET @First = '04/02/2008 05:23:22'
SET @Second = getdate()

SELECT DATEDIFF(day,@First,@Second)*24 as TotalHours,
DATEDIFF(day,@First,@Second)*24*60 as TotalMinutes,
DATEDIFF(day,@First,@Second)*24*60*60 as TotalSeconds
at

11 comments:

  1. AnonymousApril 17, 2008 at 8:14 AM

    I disagree.
    DECLARE @First datetime
    DECLARE @Second datetime
    SET @First = '04/17/2008 08:00:00'
    SET @Second = getdate()

    SELECT DATEDIFF(hour,@First,@Second) as TotalHours,
    DATEDIFF(minute,@First,@Second) - (DATEDIFF(hour,@First,@Second) * 60) as TotalMinutes,
    DATEDIFF(second,@First,@Second) - (DATEDIFF(minute,@First,@Second) * 60) as TotalSeconds
    would give a meaningful hours (plus) minutes (plus) seconds.
    Leaving the subtraction out of the second and third columns would give independent totals, like 2 hours 178 minutes rather than 2 hours 58 minutes (with the subtraction).

    ReplyDelete
  2. Suprotim AgarwalApril 17, 2008 at 7:28 PM

    Hi,

    Thanks for sharing the comment.
    The sql snippet calucates hours, minutes and seconds irrespective of each other. That's why the column names are written as TotalHours, TotalMinutes, TotalSeconds. As you mentioned, if anyone wants to get independent totals, he should not subtract.

    ReplyDelete
  3. AnonymousAugust 18, 2008 at 2:16 AM

    Hi,

    Thanks it helps me lots. i whichever was searching i got it from this.

    ReplyDelete
  4. AnonymousSeptember 1, 2008 at 6:39 AM

    if this is independent, it wont work. example is 1:59 am and 2:15 am

    ReplyDelete
  5. AnonymousSeptember 2, 2008 at 2:32 PM

    I do not think the author intended to make it work on just the time. I guess the sample as a whole works out quiet well!!

    ReplyDelete
  6. AnonymousOctober 1, 2008 at 10:17 AM

    If you want the difference between two datetimes calculated into different columns, here is a much faster way:

    floor(DATEDIFF(ss,date1,date2)/3600) as Hours,
    floor(DATEDIFF(ss,date1,date2)/60)- floor(DATEDIFF(ss,date1,date2)/3600)*60 as Minutes,
    floor(DATEDIFF(ss,date1,date2))- floor(DATEDIFF(ss,date1,date2)/60)*60 as TotalSeconds

    ReplyDelete
  7. Peeyush AgrawalNovember 12, 2008 at 10:13 PM

    This comment has been removed by the author.

    ReplyDelete
  8. Peeyush AgrawalNovember 12, 2008 at 10:16 PM

    Try this UDF to calucalte the exact difference:

    http://www.sqlmag.com/Article/ArticleID/48764/sql_server_48764.html

    ReplyDelete
  9. AnonymousJanuary 26, 2009 at 10:05 PM

    did anyone understand the code at http://www.sqlmag.com/Article/ArticleID/48764/sql_server_48764.html ??

    the code has something called callout A, B, C etc...

    does anyone know what this is

    ReplyDelete
  10. AnonymousJanuary 29, 2009 at 5:34 AM

    What is that link got to do with this post?

    ReplyDelete
  11. mrhassellJanuary 26, 2011 at 4:48 PM

    Why not just use the ss parameter or any of the other valid options and use date1 / date 2 in a single select am I mising something, like the point to all the FLOOR and math?

    DATEDIFF(ss, [CreateDateTime],[ActionUpdateDateTime]) AS [ExecutionSeconds]

    year
    yy, yyyy

    quarter
    qq, q

    month
    mm, m

    dayofyear
    dy, y

    day
    dd, d

    week
    wk, ww

    hour
    hh

    minute
    mi, n

    second
    ss, s

    millisecond
    ms

    microsecond
    mcs

    nanosecond
    ns

    ReplyDelete