CODEWITHSUNDEEP
c#sql.netsql-serverdatabase
Gaurav Gupta
Gaurav Gupta

Reputation: 139

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

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?

Upvotes: 2

Views: 1960

Answers (2)

Red Devil
Red Devil

Reputation: 2393

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;

Upvotes: 0

GMB
GMB

Reputation: 222492

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
;

Upvotes: 3

Related Questions