2

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

0

2 Answers 2

11

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
-3

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.

2
  • Why use the datetime2 type if CAST alone resolves the issue?
    – Andriy M
    Mar 30, 2016 at 16:57
  • 1
    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.
    – AMtwo
    Mar 30, 2016 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.