StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>this will give you what you are after:</p> <pre><code>CREATE TABLE #Times ( Column1 datetime ,Column2 datetime ) INSERT INTO #Times VALUES ('1/1/1900 12:20:45', '1/1/1900 23:22:25') INSERT INTO #Times VALUES ('1/1/1900 09:00:00', '1/1/1900 18:10:30') SELECT CONVERT(varchar(10),dateadd(day,datediff(day,0,dt2.MinColumn1),0),101) +' ' +CASE WHEN TotalSecondsColumn1/3600>60 then '60' ELSE CONVERT(varchar(2),TotalSecondsColumn1/3600) END +':' +CASE WHEN (TotalSecondsColumn1-(TotalSecondsColumn1/3600*3600))/60 >60 then '60' ELSE CONVERT(varchar(2),(TotalSecondsColumn1-(TotalSecondsColumn1/3600*3600))/60) END +':' +CASE WHEN TotalSecondsColumn1-((TotalSecondsColumn1/3600*3600)+(((TotalSecondsColumn1-(TotalSecondsColumn1/3600*3600))/60)*60)) >60 then '60' ELSE CONVERT(varchar(2),TotalSecondsColumn1-((TotalSecondsColumn1/3600*3600)+(((TotalSecondsColumn1-(TotalSecondsColumn1/3600*3600))/60)*60))) END AS Column1 ,CONVERT(varchar(10),dateadd(day,datediff(day,0,dt2.MinColumn2),0),101) +' ' +CASE WHEN TotalSecondsColumn2/3600>60 then '60' ELSE CONVERT(varchar(2),TotalSecondsColumn2/3600) END +':' +CASE WHEN (TotalSecondsColumn2-(TotalSecondsColumn2/3600*3600))/60 >60 then '60' ELSE CONVERT(varchar(2),(TotalSecondsColumn2-(TotalSecondsColumn2/3600*3600))/60) END +':' +CASE WHEN TotalSecondsColumn2-((TotalSecondsColumn2/3600*3600)+(((TotalSecondsColumn2-(TotalSecondsColumn2/3600*3600))/60)*60)) >60 then '60' ELSE CONVERT(varchar(2),TotalSecondsColumn2-((TotalSecondsColumn2/3600*3600)+(((TotalSecondsColumn2-(TotalSecondsColumn2/3600*3600))/60)*60))) END AS Column1 FROM #Times t INNER JOIN (SELECT SUM(DATEDIFF(second,CONVERT(datetime,LEFT(CONVERT(char(23),Column1,121),10)),Column1)) AS TotalSecondsColumn1 ,SUM(DATEDIFF(second,CONVERT(datetime,LEFT(CONVERT(char(23),Column2,121),10)),Column2)) AS TotalSecondsColumn2 FROM #Times ) dt ON 1=1 INNER JOIN (SELECT MIN(Column1) AS MinColumn1 ,MIN(Column2) AS MinColumn2 FROM #Times ) dt2 ON 1=1 WHERE t.Column1=dt2.MinColumn1 </code></pre> <p>OUTPUT:</p> <pre><code>Column1 Column1 ------------------- ------------------- 01/01/1900 21:20:45 01/01/1900 41:32:55 (1 row(s) affected) </code></pre> <p>However, this will accurately sum the times, incrementing the day, month, and year as well:</p> <pre><code>SELECT DATEADD(second,dt.TotalSecondsColumn1,dateadd(day,datediff(day,0,dt2.MinColumn1),0)) AS Column1 ,DATEADD(second,dt.TotalSecondsColumn2,dateadd(day,datediff(day,0,dt2.MinColumn2),0)) AS Column2 FROM #Times t INNER JOIN (SELECT SUM(DATEDIFF(second,CONVERT(datetime,LEFT(CONVERT(char(23),Column1,121),10)),Column1)) AS TotalSecondsColumn1 ,SUM(DATEDIFF(second,CONVERT(datetime,LEFT(CONVERT(char(23),Column2,121),10)),Column2)) AS TotalSecondsColumn2 FROM #Times ) dt ON 1=1 INNER JOIN (SELECT MIN(Column1) AS MinColumn1 ,MIN(Column2) AS MinColumn2 FROM #Times ) dt2 ON 1=1 WHERE t.Column1=dt2.MinColumn1 </code></pre> <p>OUTPUT:</p> <pre><code>Column1 Column2 ----------------------- ----------------------- 1900-01-01 21:20:45.000 1900-01-02 17:32:55.000 (1 row(s) affected) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload