Error converting from nvarchar to datetime in stored procedure

avvweb
avvweb used Ask the Experts™
on
I'm getting an error with this stored procedure:

select @SQL4 = 'insert into dbo.Micos_Modules_Boletos_Logs'+CONVERT(nvarchar, @Evento)+' (Boleto, Maquina, Resultado, Fecha) values ('''+@Boleto+''', '''+@Maquina+''', ''' + CONVERT(nvarchar, @Resultado)+''', @Fecha);'
      select @Params3 = '@Fecha datetime'
      exec sp_executesql @SQL4, @Params3, GETDATE

Error converting from data type nvarchar to datetime

Help please

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Use getdate() with the brackets
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Try using

select @SQL4 = 'insert into dbo.Micos_Modules_Boletos_Logs'+CONVERT(nvarchar, @Evento)+' (Boleto, Maquina, Resultado, Fecha) values ('''+@Boleto+''', '''+@Maquina+''', ''' + CONVERT(nvarchar, @Resultado)+''', convert(datetime, @Fecha, 101));'

select @Params3 = '@Fecha datetime'
   
exec sp_executesql @SQL4, @Params3, GETDATE

Just replace 101 with the style you would be providing the date input as per

http://msdn.microsoft.com/en-us/library/ms187928.aspx
your last variable "@Fecha" in INSERT statement is not considered as variable as it is in single quote so will work as string itself. however this is not the issue you are asking but without seeing your complete SPs, I can't help you much. even give below statement a try:

exec sp_executesql @SQL4, @Params3, cast(GETDATE() as nvarchar(max))
     

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial