how to convert between 2 date time into D0:HH:MM:SS | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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

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
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)

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

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)
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

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |