CODEWITHSUNDEEP
sql-servert-sqldatetime
LittleDragon
LittleDragon

Reputation: 2437

Conversion failed converting date and/or time from character string

I'm writing a dynamic query and I need to cast a datetime variable as a time variable

DECLARE @pdatetime_GarageOpenFrom DATETIME = '2013-07-25 08:00:00'

declare  @str_SQLCount2 varchar(max)
set @str_SQLCount2 = 'select (CONVERT(CHAR(10), @pdatetime_GarageOpenFrom, 111)'
print(@str_SQLCount2)
exec(@str_SQLCount2)

Upvotes: 0

Views: 830

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269447

Of course this generates an error. The variable @pdatetime_GarageOpenFrom is not known in the context of the exec statement.

You basically have two options. Either declare the variable in the sql string or use sp_executesql().

declare  @str_SQLCount2 varchar(max)
set @str_SQLCount2 = '
DECLARE @pdatetime_GarageOpenFrom DATETIME = ''2013-07-25 08:00:00''
select CONVERT(CHAR(10), @pdatetime_GarageOpenFrom, 111)'
print(@str_SQLCount2)
exec(@str_SQLCount2)

The preferred method is sp_execute_sql with your original string:

DECLARE @pdatetime_GarageOpenFrom DATETIME = '2013-07-25 08:00:00'

declare  @str_SQLCount2 varchar(max);
set @str_SQLCount2 = 'select CONVERT(CHAR(10), @pdatetime_GarageOpenFrom, 111)';
print(@str_SQLCount2);

exec sp_executesql @str_SQLCount2,
                   N'@pdatetime_GarageOpenFrom DATETIME',
                   @pdatetime_GarageOpenFrom = @pdatetime_GarageOpenFrom;

Upvotes: 1

Related Questions