Datetime datatype stores date and time part. If you have only date value, You can add time part to a datetime variable using direct literal value and + operator.
It is shown below
DECLARE @date DATETIME
SET @date='2010-10-01'
SET @date=@date+'15:00:00'
SELECT @date AS DATETIME
The result is 2010-10-01 15:00:00.000
But it is not possible to add it using a time variable
DECLARE @date DATETIME, @time time
SET @date='2010-10-01'
SET @time='15:00:00'
SET @date=@date+@time
SELECT @date
The error is
Msg 402, Level 16, State 1, Line 4
The data types datetime and time are incompatible in the add operator.
So the solution is to convert time datatype into datetime and add
DECLARE @date DATETIME, @time time
SET @date='2010-10-01'
SET @time='15:00:00'
SET @date=@date+CAST(@time AS DATETIME)
SELECT @date AS DATETIME
The result is 2010-10-01 15:00:00.000
So when adding data of datetime and time datatype values, you need to be aware of this.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
This looks good. Same issue I am facing while converting data from Varchar(max) to BIGINT / float. it gives me the error error converting varchar to numeric. searched a lot in google for solution but didn’t worked. pls suggest with following syntax:
INSERT INTO (SrNo,ShortOrder,FinDateId,CIN,Heading,Description,Year1,Year2,Year3)
SELECT 32,43,FinDateId,CIN,’Shareholders Fund’,’Total Shareholders Fund’,
CAST(SUM(CONVERT(float, ISNULL(Year1,’0′) )) AS Varchar(MAX) ) AS Year1,
CAST(SUM(CONVERT(float, ISNULL(Year2,’0′) )) AS Varchar(MAX) ) AS Year2,
CAST(SUM(CONVERT(float, ISNULL(Year3,’0′) )) AS Varchar(MAX) ) AS Year3
FROM
WHERE ShortOrder IN (12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42)
AND FinDateId =@finDateId AND CIN = @CIN
GROUP BY CIN,FinDateId
HAVING CIN = @CIN AND FinDateId = @finDateId
There is no need for casting, its working without casting and your query without casting is not giving any error
Bhavesh, it will work fine in SQL 2008/2008 R2. The conversion issue comes from SQL 2012 and above.
Not true! If you use both DateTime datatypes it works just fine. Your error was because you used different data types. Try this:
DECLARE @date DATETIME, @time DATETIME
SET @date=’2010-10-01′
SET @time=’15:00:00′
SET @date=@date+@time
SELECT @date
Not True! if i am using underneath query in SQL 2008/2008 R2 it works fine, but through a error in SQL 2012 and above.
DECLARE @date DATETIME, @time DATETIME
SET @date=’2010-10-01′
SET @time=’15:00:00′
SET @date=@date+@time
SELECT @date
DECLARE @date DATETIME, @time time,@dtm2 datetime
SET @date=’2010-10-01′
SET @dtm2=’2017-01-04 16:01:00′
SET @time=’15:00:00′
SET @date=@date+cast(@dtm2 as time)
SELECT @date
This block of code works perfectly on DB Server1/*Version- Microsoft SQL Server 2012 */
and DB Server2/*Version – Microsoft SQL Server 2016 (SP1) */
but gives this error ” The data types datetime and time are incompatible in the add operator.” when executing on DB Server3/*Version Microsoft SQL Server 2016 (SP1) */
Could you please tell me what is causing this error on some servers only? First I thought it was due to the difference in version, but as DB Server2 and DB Server3 have the same version what is going wrong here? Also noticed this works perfectly in some of the databases in DB Server2 but gives error on some other DBs on same server. Thank you.
It all depends on database compatibility level in which you are running above query.
Why try catch is not working ? For below statement
begin Try
declare @date datetime=’2018-01-01 2:00′
select @date, CONVERT(time,getdate())
print ‘Error start’
select @date+CONVERT(time,getdate())
end try
begin catch
select ERROR_MESSAGE()
select @date+cast(getdate()as datetime)
end catch
Hi Pinal,
I am having some problem.
I want time between 2 dates. For example if I want 28 jan 2019 08:00:00 to 29 Jan 2019 07:00:00.
I am passing start and end date by SP parameter.
How I solve this ?
I’m endeavoring to reproduce this, but at the hour level. Any suggestions? Is it as simple as changing the DATEADD to HOUR and the datetypes to DATETIME?