question

monapatel728 avatar image
monapatel728 asked

How to convert negative int to time in SQL

Hello,

how can I convert -1469 and 148 (positive & negative) int to -hh:mm:ss or hh:mm:ss formate?

sql2008
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image Jeff Moden commented ·
What do the positive and negative values represent? Seconds?
0 Likes 0 ·
anthony.green avatar image
anthony.green answered

You need a starting datetime to add that on to and then you need to know the increment, do those represent seconds/minutes/hours from the starting date, or is it a case of you just need to work out the devisors and represent them as hh:mm:ss.

Two options below depending on what your requirements are

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered
declare @defaultdate datetime = '1900-01-01 00:00:00'
select right(convert(varchar(100),dateadd(second,-1469,@defaultdate),120),8), right(convert(varchar(100),dateadd(second,148,@defaultdate),120),8)


10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered
declare @negtime int = -1469, @postime int = 148 
select 
     '-'+right('00'+convert(varchar(2),((((@negtime * -1) / 60)/60)%24)),2)+':' --hour
    +right('00'+convert(varchar(2),(((@negtime * -1) / 60)% 60)),2)+':' --minutes
    +right('00'+convert(varchar(2),((@negtime *-1) % 60)),2) --second
,
     right('00'+convert(varchar(2),(((@postime / 60)/60)%24)),2)+':' --hour
    +right('00'+convert(varchar(2),((@postime / 60)% 60)),2)+':' --minutes
    +right('00'+convert(varchar(2),(@postime % 60)),2) --second
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.