SQL Server Performance

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

Discussion in 'SQL Server 2005 General DBA Questions' started by avipenina, Nov 8, 2010.

  1. avipenina New Member

    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 D00:HH:MM:SS,and if i can make it in one syntax(no update)?
    THX
    avipenina, Nov 8, 2010
    #2

  2. FrankKalis Moderator

    NOt sure if I understand you, but wouldn't this work?
    CONVERT(CHAR(8), DATEADD(SECOND,convert(int, datediff (ss,start_date,end_date)), '00:00:00'), 108)
    FrankKalis, Nov 9, 2010
    #3

  3. avipenina New Member

    almost.
    what if the results from the 2 columns are more then 24hours?
    i need the Day before the HH:MM:SS like D00:HH:MM:SS.
    THX
    avipenina, Nov 9, 2010
    #4

  4. FrankKalis Moderator

    I'm not really sure, if I would do this formatting in the server, but here is one way to get what you want:
    DECLARE @StartDate datetime;
    DECLARE @EndDate datetime;

    SELECT
    @StartDate = '2010-11-10 12:00:00.000',
    @EndDate = '2010-11-11 13:21:10.000';

    SELECT
    'D'+ REPLACE(STR(foo.TheTime / 86400, 2), ' ', '0')
    + ':' +
    REPLACE(STR((foo.TheTime / 86400) / 3600, 2), ' ', '0')
    + ':' +
    REPLACE(STR((foo.TheTime / 60) % 60, 2), ' ', '0')
    + ':' +
    REPLACE(STR(foo.TheTime % 60 , 2), ' ', '0') AS TheDate,

    foo.TheTime / 86400 AS [Days],
    (foo.TheTime / 86400) / 3600 AS [Hours],
    (foo.TheTime / 60) % 60 AS [Minutes],
    foo.TheTime % 60 AS [Seconds]
    FROM
    (SELECT
    DATEDIFF(ss,@StartDate, @EndDate) AS TheTime
    ) foo
    TheDate Days Hours Minutes Seconds
    D01:00:21:10 1 0 21 10

    (1 row(s) affected)

    FrankKalis, Nov 10, 2010
    #5

  5. Madhivanan Moderator

    This is the formation issue that should be done in your front end application. If there is no option, another method
    DECLARE @StartDate datetime;DECLARE @EndDate datetime;SELECT @StartDate = '2010-11-10 12:00:00.000', @EndDate = '2010-11-11 13:21:10.000';select DAY(new_date)-1,convert(char(12),new_date,108) from(SELECT DATEADD(day,0,convert(char(12),@StartDate,108))+DATEADD(day,0,convert(char(12),@EndDate ,108)) as new_date) as t
    Madhivanan, Nov 11, 2010
    #6

Share This Page