# 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. ### avipeninaNew 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
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
2. ### FrankKalisModerator

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)
3. ### avipeninaNew 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
4. ### FrankKalisModerator

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)