Using DateTime Variable In Where Clause

Question

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

Answer 1

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

Answer 2

Declare the @datetime as a datetime2.

Then alter the @sql query to:-

Set @sql = 'Select * FROM #dinonuggets WHERE saledate >= ''' + cast( @datetime as varchar(23)) + ''' '

This will get around the date formating issue as well as allowing the correct string concatenation. Without the cast you would receive this error:-

Msg 402, Level 16, State 1 The data types varchar and datetime2 are incompatible in the add operator.

• Andriy M: Why use the datetime2 type if CAST alone resolves the issue?
• anon: Casting to varchar to include as a string in dynamic SQL means that when the dynamic SQL runs, it has to do an implicit conversion back to datetime in order to query #DinoNuggets. Best to just parameterize the dynamic SQL and use sp_executesql.