How to add date and time in SQL Server

Question

I have two variables @date of type datetime and @time of type time. I want to add both to get another datetime variable. And I want to perform further calculations on it.

Ex:

Declare @date datetime
Declare @time time

I want something like this

@date = @date + @time (but not concatenation)

SELECT @Startdate = DATEADD(DAY, -1, @date )

Is there any way?

not even after doing set @date it is just concatenating both fields. i will not be perform dateadd operation. — user2045931, Jul 17 '15 at 07:59
possible duplicate of [Combining (concatenating) date and time into a datetime](http://stackoverflow.com/questions/18622384/combining-concatenating-date-and-time-into-a-datetime) — Jodrell, Jul 17 '15 at 08:44

Andrey Korneyev · Answer 1 · 2015-07-17T07:58:17.543

If you need to take only date part from @date and time part from @time - can convert your @date and @time to strings, concatenate the values and convert back to datetime:

select cast(convert(nvarchar(20), @date, 104) + ' ' +
            convert(nvarchar(20), @time, 108) as datetime2)

Or, alternatively, if you need to add time to datetime value, you can do something like:

select dateadd(ms, 
           datepart(ms, @time), 
           dateadd(ss, 
                   datepart(ss, @time), 
                   dateadd(mi, 
                           datepart(mi, @time), 
                           dateadd(hh, datepart(hh, @time), @date))))

i tried this. but its not working. its not doing the it. but its not performing SELECT @Startdate = DATEADD(DAY,-1,@date ) — user2045931, Jul 17 '15 at 07:57

score 0 · Accepted Answer · answered Jul 17 '15 at 07:59

You can tranform your time to seconds and add them to your datetime value:

DECLARE @datetime DATETIME = GETDATE(),
        @time TIME = '01:16:24',
        @timeinseconds INT

PRINT 'we add ' + CAST(@time AS VARCHAR(8)) + ' to ' + CONVERT(VARCHAR,@datetime,120)+ ':'


SELECT @timeinseconds = DATEPART(SECOND, @time)
                        + DATEPART(MINUTE, @time) * 60
                        + DATEPART(HOUR, @time) * 3600

SET @datetime = DATEADD(SECOND,@timeinseconds,@datetime)

PRINT 'The result is: ' + CONVERT(VARCHAR,@datetime,120)

Output:

we add 01:16:24 to 2015-07-17 09:58:45:
The result is: 2015-07-17 11:15:09

Stanislovas Kalašnikovas · Answer 3 · 2015-07-17T08:05:09.963

0

First of all convert @date and @time variables to NVARCHAR(), then concat them and after It convert It to DATETIME datatype. After It you can use DATEADD function on It. Try in following:

DECLARE @date DATETIME 
DECLARE @time TIME

SET @date = GETDATE()
SET @time = '10:12:13'

SELECT DATEADD(DAY, -1, CAST(CONVERT(NVARCHAR(20), @date, 110) + ' ' +
                             CONVERT(NVARCHAR(20), @time, 108) AS DATETIME))

OUTPUT (Today day -1 + time '10:12:13'):

2015-07-16 10:12:13.000

edited Jul 17 '15 at 08:05

answered Jul 17 '15 at 08:01

Stanislovas Kalašnikovas

8,935
4
29
59

What should this be doing? Can you explain your idea behind this? – CeOnSql Jul 17 '15 at 08:05

score 0 · Answer 4 · answered Sep 08 '20 at 19:29

0

Your code is correct.

DECLARE @date DATETIME = '1/1/2020'
DECLARE @time TIME = '1:00 pm'
DECLARE @Startdate DATETIME

SET @date = @date + @time

SELECT @Startdate = DATEADD(DAY, -1, @date)

@date = 2020-01-01 13:00:00.000
@Startdate = 2019-12-31 13:00:00.000

It isn't concatenating, it is adding them together. The time on @date is 0:00:00.000, so it might appear to be concatenating them. But change @date to '1/1/2020 1:00 am' and then:

@date = 2020-01-01 14:00:00.000
@Startdate = 2019-12-31 14:00:00.000

answered Sep 08 '20 at 19:29

Bryan

157
2
12

Doesn't work: "The data types datetime and time are incompatible in the add operator." – Bryan Williams Jun 02 '22 at 14:03
Interesting that it doesn't work for you. I just copied and pasted it into SSMS, and it still works exactly as I have it above. At the time I was using SSMS v17, now I am using v18. But it shouldn't make a difference, as you can always add TIME to a DATETIME variable. So I'm not sure it deserved a down vote. – Bryan Jun 03 '22 at 16:49

score 0 · Answer 5 · answered Jun 02 '22 at 14:17

The only thing you are missing is that @time needs to be cast back to a datetime before adding to @date.

declare @date datetime = '2022-05-26'
declare @time time = '09:52:14'
declare @Startdate datetime

set @date = @date + convert(datetime,@time)

SELECT @Startdate = DATEADD(DAY, -1, @date)

Produces:

score -1 · Answer 6 · answered Jul 17 '15 at 08:06

-1

I'm not sure what's going on here, but if your variables are datetime and time types, this should work just fine:

declare @date datetime
declare @time time

set @date = '20150717'
set @time = '12:34:56'

set @date = @date + @time

select @date, DATEADD(DAY,-1,@date)

See SQL Fiddle

If the problem is that @date contains also time part, you can use:

set @date = convert(datetime, convert(date, @date)) + @time

answered Jul 17 '15 at 08:06

James Z

12,104
10
27
43

Error: "The data types datetime and time are incompatible in the add operator." – Bryan Williams Jun 02 '22 at 14:06
@BryanWilliams I'm guessing something has changed over the years, at least based on this question: https://stackoverflow.com/questions/40514726/the-data-types-date-and-datetime-are-incompatible-in-the-add-operator – James Z Jun 02 '22 at 15:01

How to add date and time in SQL Server

6 Answers6