How to convert negative time span to positive in SQL server

Question

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.

Raja T · 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) 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

waqas shah · Answer

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