Your dynamic query looks OK, but conversion between
varchar
data type to
datetime
data type failed, becuase the SQL Server uses different
dateformat
.
See:
SET DATEFORMAT (instruction)[
^]
declare @datetime varchar(10)
set @datetime ='14/05/2015'
SET DATEFORMAT dmy;
SELECT CONVERT(DATETIME, @datetime) AS MyDate
Now, you should be able to add daata.
Suggested correction for your query code:
declare @userId varchar(10)
declare @sql varchar(100)
declare @datetime varchar(10)
set @userId =2
set @datetime ='14/05/2015';
set @sql= 'SET DATEFORMAT dmy;' +
'insert into Table1(UserId, dt)'+
'values(' + @userId + ',''' + @datetime + ''')'
EXEC sp_executesql @sql