Post reply

how to convert between 2 date time into D0:HH:MM:SS

  • November 8, 2010 at 1:54 am

    #157304

    hi,

    i've 2 columns 1 with start date and the other with end date.

    so far i run this syntax to get the datediff

    datediff (ss,start_date,end_date) then i update the results into

    CONVERT(CHAR(8), DATEADD(SECOND,convert(int, results_column), '00:00:00'), 108)

    my question is how i convert the results from those 2 columns into a D0:HH:MM:SS,and if i can make it in one syntax(no update)?

    THX

  • November 8, 2010 at 11:45 am

    #1247816

    How does this work for you?

    declare @date1 datetime,

    @date2 datetime,

    @SwapDate datetime;

    SELECT @Date2 = GetDate(),

    @Date1 = '20000101';

    if @Date2 > @Date1

    SELECT @SwapDate = @Date2,

    @Date2 = @Date1,

    @Date1 = @SwapDate;

    WITH CTE (DateDelta) AS

    (

    SELECT @Date1 - @Date2

    )

    SELECT CONVERT(varchar(10), DateDiff(day, 0, DateDelta)) + ':' +

    CONVERT(varchar(12), DateDelta, 114)

    FROM CTE;

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 2 posts - 1 through 1 (of 1 total)

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