How to add datetime field with a time field

Question

I have to add time value to existing datetime value using T-SQL in SQL Server 2012.

I was thinking that DATEADD function it might be a solution, but it is not...

Perhaps I have somehow time convert to datetime?

So I have

StartDate 2013-02-18 18:34:40.330 (datetime)

Interval 00:11:00.0000000 (time)

EndDate ? tsql ? (datetime)

Any clue?

Solution 1

Try something like this. Note: I am not taking milliseconds here

declare @dt datetime = getdate()
declare @t time = '01:35:45'

select dateadd(second, 
                  datepart(hour,@t) * 3600 + 
                  datepart(minute,@t) * 60 + 
                  datepart(second,@t),
                  @dt)

Solution 2

DECLARE @d DATETIME = '2013-02-18T18:34:40.330',
        @t TIME(7)  = '00:11:00.0000000';

SELECT EndDate = DATEADD(SECOND, DATEDIFF(SECOND, 0, @t), @d);

Result:

EndDate
-----------------------
2013-02-18 18:45:40.330

Now, you really shouldn't be storing interval in a time column. time is meant to represent a point in time, not a duration. What happens when the interval is >= 24 hours? You should store the start time and end time of an event (those things are typically at least as relevant as the duration), and you can always calculate the duration from those points.

Solution 3

I have been using Azure-DB lately and tried the code below there. This does not work in SQL 2012. See this post for more info on SQL-date arithmetics: Difference in datetime and time arithmetic in Azure-DB vs. sql-server 2008R2

I'm a bit late to the party but since the obvious way to do it is missing...

DECLARE @StartDate datetime
DECLARE @Interval time
DECLARE @EndDate datetime
SET @StartDate = '2013-02-18 18:34:40.330'
SET @Interval = '00:11:00.000000'

SET @EndDate = @StartDate + @Interval

SELECT @StartDate StartDate, @Interval Interval, @EndDate EndDate

Output:
StartDate                  Interval             EndDate
2013-02-18 18:34:40.330    00:11:00.0000000    2013-02-18 18:45:40.330

Comments

[NoWar]

Thanks for the last paragraph! Yes I store interval < 24 hours.

[Adir D]

But what is the point of storing interval as time? Why not just store the number of seconds?

[Michael Erickson]

When we have static tables that are entered directly by users, it is beneficial to store with time instead so it is easier to understand. This is very clever to use the DATEDIFF to get the seconds. Thanks.