Conversion failed when converting datetime from character string in store procedure

sql

Solution

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

Problem

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) ```

Original source