The data types varchar and datetime2 are incompatible in the add operator.

Question

Post reply

The data types varchar and datetime2 are incompatible in the add operator.

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
November 25, 2019 at 7:10 pm

#3700711

In the procedure i have date column as datetime2
declare @date datetime2 = null, @CaptureError varchar(100)
when i am logging this variable like below
SET @CaptureError= @CaptureError + ' ,Date: ' + coalesce(@Date, 'NULL')
this is getting error as data types varchar and datetime2 are incompatible in the add operator.
what is the workaround for this?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

rVadim Hall of Fame Points: 3969 More actions · Answer 1

Explicitly convert @Date to CHAR or VARCHAR.

--Vadim R.

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 2

SET @CaptureError= @CaptureError + ' ,Date: ' + convert(varchar(10), @Date,101)

Is this correct?

Lowell SSC Guru Points: 323518 More actions · Answer 3

you were originally planning to handle the null, so this is a bit better:

SET @CaptureError= @CaptureError + ' ,Date: ' + coalesce(convert(varchar(10), @Date,101), 'NULL')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rVadim Hall of Fame Points: 3969 More actions · Answer 4

Yes, but as written you'll get NULL because @CaptureError and @Date are nulls. Assign some values and test it.

--Vadim R.

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 5

May i know the reason why i get this error The data types varchar and datetime2 are incompatible in the add operator. when i changed variable from date time to datetime2.

Lowell SSC Guru Points: 323518 More actions · Answer 6

datatime has an implicit conversion to varchar, based on your locale. datetime2 does not.

the map below from microsoft has some other examples.

This reply was modified 4 years, 12 months ago by Lowell. Reason: wrong map!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

drew.allen SSC Guru Points: 76985 More actions · Answer 7

mcfarlandparkway wrote:

May i know the reason why i get this error The data types varchar and datetime2 are incompatible in the add operator. when i changed variable from date time to datetime2.

It's because of the way that datetime and datetime2 are stored. One of them is big-endian and the other is little-endian (I forget which is which), so you can do addition on one, but not the other.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

rVadim Hall of Fame Points: 3969 More actions · Answer 8

Lowell wrote:

datatime has an implicit conversion to varchar, based on your locale. datetime2 does not.

Lowell, I'm a bit confused. The map you posted kind of shows that datetime2 also has an implicit conversion to varchar?

--Vadim R.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 9

Instead of suffering for the type conversion - use CONCAT which will take care of the conversion for you:

Declare @CaptureError varchar(100) = 'This is an error'
      , @Date datetime2 = sysdatetime();

 Select @CaptureError, @Date, concat(@CaptureError, ', Date: ', @Date);

It even works for NULL...

Declare @CaptureError varchar(100) = 'This is an error'
      , @Date datetime2 = Null;

 Select @CaptureError, @Date, concat(@CaptureError, ', Date: ', @Date);

If you need an explicit format for the date:

Declare @CaptureError varchar(100) = 'This is an error'
      , @Date datetime2 = sysdatetime();

 Select @CaptureError, @Date, concat(@CaptureError, ', Date: ', convert(char(10), @Date, 101));

This reply was modified 4 years, 12 months ago by Jeffrey Williams.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs