• When the hour increments from one value to the next, or from 12 to 1, an hour boundary is said to have been crossed. The same happens for the other time portions too. Try this code:

    SELECT *,

    [Hours] = DATEDIFF(hour, Starttime,EndTime),

    [Minutes] = DATEDIFF(minute, Starttime,EndTime),

    Seconds = DATEDIFF(second, Starttime,EndTime),

    [millisecond] = DATEDIFF(millisecond, Starttime,EndTime),

    [microsecond] = DATEDIFF(microsecond, Starttime,EndTime)

    FROM (

    SELECT

    Starttime = cast('20140120 16:59:59.997' AS DATETIME),

    EndTime = cast('20140120 17:00:00.000' AS DATETIME)

    ) d

    There's only 3ms difference between Starttime and EndTime, but an hour boundary was crossed (16:nn:nn to 17:nn:nn), a minute boundary was crossed (16:59:nn to 17:00:nn) and a second boundary was crossed (16:59:59 to 17:00:00).

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden