Adding or subtracting time from a date and time on a SQL server

Question

I have a column offset in db like varchar(50)that contains a value such as 05:30:00or -2:15:00.

I need to add or subtract this value from another column, which is a type DATETIMElike 2011-07-22 14:51:00.

Answer 1

try something like -

select convert(datetime, '05:30:00') + GETDATE()

Answer 2

What is your database platform?

In MS SQL you will do it like this ...

-- Create some test data
create table dbo.MyData (
    Adjustment varchar(50) NOT NULL,
    BaseDate datetime NOT NULL
) on [primary]
go

insert into dbo.MyData ( Adjustment, BaseDate ) values ( '05:30:00', cast('2011-07-22 14:51:00' as datetime) )
insert into dbo.MyData ( Adjustment, BaseDate ) values ( '-2:15:00', cast('2011-06-12 10:27:30' as datetime) )
go

-- Perform the adjustment
select 
    c.Adjustment,
    c.BaseDate,
    c.AdjSecs,
    dateadd(s, c.AdjSecs, c.BaseDate ) as AdjustedDate
from (
    select 
        case 
        when left( Adjustment, 1 ) = '-' then -1 * datediff(s, 0, right( Adjustment, len(Adjustment) - 1 ))
        else datediff(s, 0, right( Adjustment, len(Adjustment) - 1 ))
        end as AdjSecs,
        Adjustment,
        BaseDate
    from dbo.MyData
) as c

Note. It also takes into account negative adjustment periods.

Answer 3

getdate()

DECLARE @mytime AS VARCHAR(10)

SET @mytime = '2:15:00'

SELECT DATEADD(
s
,CASE 
     WHEN SUBSTRING(@mytime,1,1)='-' 
           THEN -DATEDIFF(s,0, SUBSTRING(@mytime,2,LEN(@mytime)-1) 
           ELSE DATEDIFF(s,0, @mytime) 
     END
,GETDATE()
)