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.

Answer 1

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 

 

Answer 2

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