Sql-server – Using DateTime Variable In Where Clause

sql serversql-server-2008-r2t-sql

I am trying to pull all data where a sale date is >= to a certain datetime. When I execute my query I get an error of:

Msg 241, Level 16, State 1, Line 15
Conversion failed when converting date and/or time from character string.

What should I alter to get his query operational?
Below is sample DDL

Declare @datetime datetime = GetDate(), @sql varchar(max)

Create Table #dinonuggets
(
  saledate datetime
  ,item varchar(500)
)

Insert Into #dinonuggets Values
('2016-03-30 12:02:00.000','chair')
,('2016-03-04 08:12:48.000','stove')
,('2016-03-07 09:37:39.000','washer')


Set @sql = 'Select * FROM #dinonuggets WHERE saledate >= '+@datetime+' '
Print @sql
Exec (@sql)

My expected output would be

03/30/2016 12:02:00.000 chair

as that is the only data that meets the criteria

Best Answer

Your issue relates to the way you are building your dynamic SQL. If you look at the output from your PRINT statement, there are no quotes around your @datetime. Additionally, selecting from a #temp table within dynamic SQL isn't going to do what you want it to do. #dynonuggets is going to be out-of-scope for your dynamic SQL. If you really do need to work with a dynamic table, you'll need to make a global temporary table, named ##dynonuggets.

I'll assume, DynoNuggets is a real table, and you have to use dynamic SQL, and your example is just simplified for your question.

You could add quotes, but the more appropriate solution is to use sp_executesql to parameterize your dynamic SQL.

Try this:

DECLARE @datetime datetime = GETDATE(), 
        @sql nvarchar(max),
        @params nvarchar(1000)

CREATE TABLE DinoNuggets
(
  saledate datetime
  ,item varchar(500)
)

INSERT INTO DinoNuggets VALUES
('2016-03-30 12:02:00.000','chair')
,('2016-03-04 08:12:48.000','stove')
,('2016-03-07 09:37:39.000','washer')

SET @sql = N'SELECT * FROM DinoNuggets WHERE saledate >= @ParamValue';
SET @Params = N'@ParamValue datetime';
PRINT @sql;
EXEC sp_executesql @sql, @params, @ParamValue = @datetime
Related Question