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. But adding datetime and time values using variables needs one extra step.
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.
Tips for Adding Datetime and Time Safely
The CAST trick works because the DATETIME type still supports the + operator. A TIME value cast to DATETIME becomes that time on January 1, 1900, which counts as day zero, so adding it simply adds the hours, minutes and seconds.
Two things to watch. First, make sure the date part has no time already, or you will add the two times together and land on the wrong hour. If you are not sure, cast it to DATE and back to DATETIME first, which drops the time part. Second, DATETIME is only accurate to about three milliseconds, so fractional seconds from a TIME value may be rounded. Test with a time close to midnight, such as 23:59:59.999, and you will see it roll over into the next day.
The newer DATETIME2 type does not support + at all. For it, use DATEADD, for example DATEADD(SECOND, DATEDIFF(SECOND, '00:00:00', @t), CAST(@d AS DATETIME2)). This style works for every date type, so I use it in new code even when + would work.
Also ask whether you really need the date and the time in separate columns. A single DATETIME2 column is simpler to filter and sort, and you can always get each part back with CAST(Col AS DATE) and CAST(Col AS TIME). Keeping them together avoids this extra step every time you query.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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
I am sorry but where is the error in above query?
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?