Converting decimals to HH:MM:SS

Question

Converting decimals to HH:MM:SS

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

andycadley SSCertifiable Points: 5296 More actions · Answer 1

Jeff Moden - Sunday, August 12, 2018 3:20 PM
Eirikur Eiriksson - Sunday, August 12, 2018 12:46 AM
Quick question, as there is no duration data type in SQL Server, why not simply record the start and end date times and when needed, simply calculate the difference to the granularity needed?
😎
There are many pitfalls when emulating duration with the date and time data types and although this may seem trivial at the first glance, it is an accident waiting to happen.
I have to disagree put only on a purely semantic level. The DATETIME datatype calculates (Duration = EndDateTime-StartDateTime, directly) and stores durations as an offset from the "0" based date just fine. It even works with negative durations if you're trying to build a countdown. The only problem is that there isn't a convenient built-in display conversion other than format #114 (hh:mi:ss:mmm(24h)) but that's also not a huge issue.
Please see the following article for more information:
Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

Unless you strictly record all the start/end durations in UTC, it breaks if the clocks change during the duration.

Declare @start DateTimeOffset = '2018-01-01 01:00:00 +01:00' Declare @end DateTimeOffset = '2018-01-01 00:10:00 +00:00'Select DATEDIFF(minute, @start, @end)
,DATEDIFF(minute, Cast(@start as DateTime), Cast(@end as DateTime))

Jeff Moden SSC Guru Points: 1003880 More actions · Answer 2

andycadley - Sunday, August 12, 2018 3:26 PM
Jeff Moden - Sunday, August 12, 2018 3:20 PM
Eirikur Eiriksson - Sunday, August 12, 2018 12:46 AM
Quick question, as there is no duration data type in SQL Server, why not simply record the start and end date times and when needed, simply calculate the difference to the granularity needed?
😎
There are many pitfalls when emulating duration with the date and time data types and although this may seem trivial at the first glance, it is an accident waiting to happen.
I have to disagree put only on a purely semantic level. The DATETIME datatype calculates (Duration = EndDateTime-StartDateTime, directly) and stores durations as an offset from the "0" based date just fine. It even works with negative durations if you're trying to build a countdown. The only problem is that there isn't a convenient built-in display conversion other than format #114 (hh:mi:ss:mmm(24h)) but that's also not a huge issue.
Please see the following article for more information:
Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)
Unless you strictly record all the start/end durations in UTC, it breaks if the clocks change during the duration.
Declare @start DateTimeOffset = '2018-01-01 01:00:00 +01:00' Declare @end DateTimeOffset = '2018-01-01 00:10:00 +00:00'Select DATEDIFF(minute, @start, @end)
,DATEDIFF(minute, Cast(@start as DateTime), Cast(@end as DateTime))

Correct and thanks for bringing that up and sorry that I left that out of the article I cited. In fact, we have that little problem twice every year with the built in reports on the phone system because the 3rd party developers of those reports used the "local time" columns instead of the UTC columns, which are also available, because it was (apparently) too much of a hassle to do it right for something that occurs "only" twice a year.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)