|
1 |
originally posted 2015-02-13 |
This is a revised version of a solution I lifted from internet postings.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
insert @tab ([CreateDateTime]) values (convert(datetime2,'2015-02-13 12:29:01.330')) ,(convert(datetime2,'2015-02-13 12:29:34.883')) /* Note: DATETIME2 is used for better compatibility with .Net types, but it must be converted to a DATETIME before being used in the [ElapsedTimeString] calculations. */ select * from @tab select top 1 [ElapsedTimeString] = convert(nvarchar(20), convert(varchar(10), convert(int, convert(float,[LatestCurrentDateTime]) - convert(float, [EarliestDateTime]) ) * 24 /* hours over 24 */ + datepart(hh, [LatestCurrentDateTime] - [EarliestDateTime]) /* hours */ ) + ':' + right('0' + convert(varchar(2), datepart(mi, [LatestCurrentDateTime] - [EarliestDateTime])), 2) /* minutes */ + ':' + right('0' + convert(varchar(2), datepart(ss, [LatestCurrentDateTime] - [EarliestDateTime])), 2) /* seconds */ ) from @tab t outer apply ( select [EarliestDateTime] = convert(datetime, min([CreateDateTime])) ,[LatestCurrentDateTime] = convert(datetime, max([CreateDateTime])) from @tab ) as x order by [CreateDateTime] desc /* Input [CreateDateTime] values: '2015-02-13 12:29:01.3300000' '2015-02-13 12:29:34.8830000' [ElapsedTimeString] = 0:00:33 */</pre> |