Conversion failed when converting datetime from character string in store procedure

Question

I am getting conversion error when running the below sql in store procedure becasue of some reason I can't cast into varchar

Declare @sql varchar(100)
Declare @ddtime datetime

set @ddtime = '2012-02-03 22:14:50.057'

set @sql = 'select * from table1 where tdate='+@ddtime

exec(@sql)

Answer 1

You need to convert the @ddtime to a varchar since you are including it in a string:

Declare @sql varchar(100)
Declare @ddtime datetime

set @ddtime = '2012-02-03 22:14:50.057'

set @sql =  'select * from table1 where tdate= '''+ convert(nvarchar(25), @ddtime, 121) + ''''

exec(@sql)

or just have you @ddtime as a varchar instead of a datetime.

Declare @ddtime varchar(25)

The convert(varchar(25), @ddtime, 121) leaves your date in the same format that you initially set it as.

How to format datetime & date in Sql Server