SQL date format [h]:mm:ss like Excel does, beyond

Question

There is a translation for Excel built-in date format [h]:mm:ss in T-SQL or any SQL platform?

example:

    General [h]:mm:ss   [mm]:ss     [ss]
   1.00000  24:00:00    1440:00         86400
   1.00000  24:00:00    1440:00         86400

SUM

   2.00000  48:00:00    2880:00         172800

For T-SQL I will select

Without century (yy) (1)    With century (yyyy) Standard    Input/Output (3)
  14                             114                   -    hh:mi:ss:mmm(24h)

Or by default 108, I am discerning is do I have to recreate a function that can do the hh:mi:ss:mmm(+24h)?

Comments are very welcome;

Answer 1

While SQL Server's time datatype is supposed to map to the CLR's timespan datatype, it only supports intervals less than 24 hours.

SQL Server's datediff is also tricky when it comes to calculating an interval as it calculates the datepart boundaries crossed between two datetimes. For example, this should be an interval of 1 second, but datediff will tell you it's an hour if you ask for hours:

select datediff(hh, '2013-01-01T23:59:59', '2013-01-02T00:00:00')
-- Returns 1

As such, you might have to roll your own function when it comes to this, though hopefully this answer might get you started:

-- Setup test data
declare @minDate datetime = '2012-12-12 20:16:47.160'
declare @maxDate datetime = '2012-12-17 15:10:12.050'

-- Get timespan in hh:mi:ss
select cast(
        (cast(cast(@maxDate as float) - cast(@minDate as float) as int) * 24) /* hours over 24 */
        + datepart(hh, @maxDate - @minDate) /* hours */
        as varchar(10))
    + ':' + right('0' + cast(datepart(mi, @maxDate - @minDate) as varchar(2)), 2) /* minutes */
    + ':' + right('0' + cast(datepart(ss, @maxDate - @minDate) as varchar(2)), 2) /* seconds */

-- Returns 114:53:24