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

score 2 · Answer 1 · answered Jun 02 '22 at 14:17

2

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:

answered Jun 02 '22 at 14:17

Bryan Williams

452
1
5
17

It seems that in modern versions of SqlServer it's now impossible to add DateTime and Time, we always get the "The data types datetime and time are incompatible in the add operator" Error. @bryan-Williams solution's should now be the accepted solution to add DateTime and Time variables. – nmariot Sep 14 '22 at 08:02

score 1 · 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

Andrey Korneyev · Answer 3 · 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

Stanislovas Kalašnikovas · Answer 4 · 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

9,203
4
32
59

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

score -1 · Answer 5 · 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,209
10
24
44

1

Error: "The data types datetime and time are incompatible in the add operator." – Bryan Williams Jun 02 '22 at 14:06
1

@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

score -1 · Answer 6 · answered Sep 08 '20 at 19:29

-1

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

155
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
Doesn't work for me also : "The data types datetime and time are incompatible in the add operator". I'm using SSMS v18 with a SqlServer 2019 instance – nmariot Sep 14 '22 at 07:56

How to add date and time in SQL Server

6 Answers6