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

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

     

     

  • Explicitly convert @Date to CHAR or VARCHAR.

    --Vadim R.

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

     

    Is this correct?

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

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

    --Vadim R.

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

  • 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 1 year 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!

  • 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

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

  •  

    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));

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

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

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