Post reply

time format conversion

  • November 26, 2007 at 6:17 am

    #62196

    if datediff is used to give no of milliseconds b/w dateA and dateB. How can this be converted in 00:00:00 format. I don't want to display date.

  • November 26, 2007 at 6:34 am

    #754001

    Try this:

    declare

    @dateA datetime,

    @dateB datetime,

    @baseDate datetime

    set @dateA = '11/26/2007 13:27:53.205'

    set @dateB = '11/26/2007 13:27:53.209'

    set @baseDate = '07/04/1923 00:00:00.000'

    select convert(char(12), dateadd(ms, datediff(ms, @dateA, @dateB), @baseDate), 114)

    Bear in mind that the minimum difference will be 3 ms (as demo'd above)...

    HTH,

    Art

  • November 26, 2007 at 8:58 am

    #754108

    Thanks R2ro

  • November 26, 2007 at 9:11 am

    #754113

    Just so's ya know...

    The date chosen for the base date (@baseDate) is unimportant except that it must be in range. The important part is the time component of the base date. You may want to consider selecting a 'standard' base date (e.g. 1/1/1900 or 1/1/1753) until SQL2008 is available. It has separate (and various) date and time datatypes.

  • November 27, 2007 at 4:21 am

    #754460

    Thanks R2ro

    Appreciate your support.

  • November 27, 2007 at 4:51 am

    #754475

    declare

    @dateA datetime,

    @dateB datetime,

    @baseDate datetime

    set @dateA = '11/26/2007 13:27:53.205'

    set @dateB = '11/26/2007 13:27:53.209'

    set @baseDate = '07/04/1923 00:00:00.000'

    select convert(char(11),dateadd(ms, datediff(ms, @dateA, @dateB), @baseDate),114)

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

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