question

MonikaPatel-9889 avatar image
0 Votes"
MonikaPatel-9889 asked MonikaPatel-9889 answered

Negative and Positive int to time

hello,

-1469
-5776
-2952
-6790
-815
-2486
24
3922
NULL
-323
-1023
148

I am trying to convert this int into time, but it is not working properly. I need in -hh:mm:ss or HH:MM:SS formate.

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @MonikaPatel-9889,

Welcome to the microsoft TSQL Q&A forum!

If there is a minus sign, it cannot be converted to time format.

Time range 00:00:00 through 23:59:59.997

Unless the minus sign is removed.

For more details, please refer to:
datetime (Transact-SQL)

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MonikaPatel-9889 avatar image
0 Votes"
MonikaPatel-9889 answered

Hi Eco,

You cleared my doubt, I am trying to convert that since long time. Thank You.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MonikaPatel-9889 avatar image
0 Votes"
MonikaPatel-9889 answered

hello,

I used *

'select CAST(DATEADD(second, column / 100 * 60 + column % 100, 0) AS time)
from table

*'
for positive time and it working successfully and resulted as timespan . but, not for negative.








5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MonikaPatel-9889 avatar image
0 Votes"
MonikaPatel-9889 answered Viorel-1 commented

hello,

Thanks for the answer

But, I need that in timeformate, it resulted as varchar.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


The time datatype does not seem to support negative values. Maybe you should keep your durations as numbers. Interpret or convert them when needed depending on circumstances.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

If these numbers represents seconds extracted from some column, then:

 select iif(MyColumn < 0, '-', '') + convert(char(8), dateadd(second, abs(MyColumn), cast('0:00' as time)), 114)
 from MyTable

This assumes that the interval does not exceed 24 hours.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.