How to fix 'The data types datetime and uniqueidentifier are incompatible in the add operator.'

user_10839538 0
Viewed 1096
DECLARE @caseId UNIQUEIDENTIFIER,
    @fieldId UNIQUEIDENTIFIER,
    @columnName NVARCHAR(MAX),
    @dateValue DATETIME,
    @tableName nvarchar(MAX);

set @command = 'update ' + @tableName + ' set ' + @columnName + ' = ' + @dateValue + ' where Id = ''' + @caseId + '''';
exec sp_executesql @command;

Getting error while running the update command. - The data types datetime and uniqueidentifier are incompatible in the add operator. Can someone help?

sql-server sql database .net c#
2 Answers

I think that you should use query parameters for the date and id values rather than mungling them in the query string.

That would look like:

declare 
    @pCaseId UNIQUEIDENTIFIER,
    @pFieldId UNIQUEIDENTIFIER,
    @pColumnName NVARCHAR(MAX),
    @pDateValue DATETIME,
    @pTableName nvarchar(MAX);

set @command = 'update ' + @pTableName + ' set ' + @pColumnName + ' = @dateValue where Id = @caseId';
exec sp_executesql 
    @command,
    N'@dateValue DATETIME, @caseId UNIQUEIDENTIFIER',
    @pDateValue, 
    @pCaseId
;
user_106767160

Try this:

DECLARE @caseId UNIQUEIDENTIFIER,
    @fieldId UNIQUEIDENTIFIER,
    @columnName NVARCHAR(MAX),
    @dateValue DATETIME,
    @tableName nvarchar(MAX);

set @command = 'update ' + @tableName + ' set ' + @columnName + ' = ' +convert(nvarchar(100),@dateValue,120) + ' where Id = ''' + @caseId + '''';
exec sp_executesql @command;
user_62601570
Related