How to convert negative time span to positive in SQL server

waqas shah

11y3.2k

I have a situation in which some time duration is in negative format and stored as string in SQL database as

-06:34:41

and some of will be in positive format as

02:00:00 and

01:00:00

Now I need to add/sum these time duration in select query using SQL Server 2008 such that result will be

-03:34:41

and then after calculation i need it back into string format .Thanks in advance.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Raja T11y ago
Accepted answer
Hi Waqas,

Try this,

declare @timevalues table
(
timestring NVARCHAR(10)
)

insert into @timevalues
values
('-06:34:41'),
('02:00:00'),
('01:00:00')
;

with cte
as
( select case when left(timestring,1)='-' then -1 else 1 end as multiply,
right(timestring,8) as timestring,
--get hours in seconds:
DATEPART(HOUR,right(timestring,8)) * 3600 AS h_in_s,
--get minutes in seconds:
DATEPART(MINUTE,right(timestring,8)) * 60 AS m_in_s,
--get seconds:
DATEPART(SECOND,right(timestring,8)) AS s
from @timevalues
)

select case when sum((c.h_in_s + c.m_in_s + c.s) * multiply) < 0
then '-' + CONVERT(varchar,DATEADD(s,ABS(sum((c.h_in_s + c.m_in_s + c.s) * multiply)),0),114)
else CONVERT(varchar,DATEADD(s,sum((c.h_in_s + c.m_in_s + c.s) * multiply),0),114)
end as new_time_string
from cte c

Refer below url

http://stackoverflow.com/questions/31376938/how-to-convert-negative-time-span-to-positive-in-sql-server-2008

Thanks
0
waqas shah11y ago
Thanks for your kind reply and time.Your mentioned link was my own question on another blog and i got answer from that blog.
0