SQL SERVER – Adding Datetime and Time Values Using Variables

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)

Previous Post
SQL SERVER – Who is consuming CPU on my SQL Server?
Next Post
SQLAuthority News – 9th Year Blog Anniversary

Related Posts

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

    Reply
  • There is no need for casting, its working without casting and your query without casting is not giving any error

    Reply
    • nakulvachhrajani
      October 31, 2015 3:49 pm

      Bhavesh, it will work fine in SQL 2008/2008 R2. The conversion issue comes from SQL 2012 and above.

      Reply
  • 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

    Reply
    • Himanshu Saxena
      August 25, 2017 3:04 pm

      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

      Reply
  • 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.

    Reply
  • Udhayaganesh Pachiyappan
    September 11, 2018 1:21 pm

    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

    Reply
  • 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 ?

    Reply
  • 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?

    Reply

Leave a Reply

Menu