Reversing time values into integer ones

Question

Hi there,

Using FORMAT and DATEADD in conjunction we can get the time value as expected from a integer format.

DECLARE @iTime INTEGER = 49920
PRINT FORMAT(DATEADD(SECOND,@iTime,'00:00:00'), 'HH:mm','es-es') + ':00.000'

What about the reverse way?

ex:

DECLARE @dTime AS TIME = '19:30:00.456'

How can we get the integer value equivalent of that?

Answer 1

For integer => time you use DATEADD, so logically for time => integer you use DATEDIFF

DECLARE @dTime AS TIME = '13:52:00.000'
SELECT DATEDIFF(second, '00:00:00', @dTime)

Answer 2

Thanks a lot Olaf