1

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;

Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
Holanda Corella
  • 67
  • 1
  • 5

1 Answers1

3

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
Community
  • 1
  • 1
Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
  • Will try this, talking with my partner to see how ti implement it because we already have data in decimals but yes indeed a good start! – Holanda Corella Feb 07 '13 at 16:59
  • I would never have thought that the T-SQL `time` was supposed to map to a timespan type on the client side. I'd always considered it a point in time, just like `datetime` or `datetime2` but without the date part. Thanks for that link, which opened (or rather *widened*) my eyes. – Andriy M Feb 09 '13 at 11:30