SQL Server - Converting to smalldatetime in dynamic SQL Statement

Question

The below dynamic SQL throws an error:

The conversion failed when converting character string to smalldatetime data type

My code:

DECLARE @pTimeStamp smalldatetime
SET @pTimeStamp = '2017-05-22 12:15:00'

DECLARE @SQLQuery AS NVARCHAR(4000)
Set @SQLQuery = N'Select *' + 
' From SampleTable'  +
' Where TimeStamp = ' + @pTimeStamp

EXECUTE sp_executesql @SQLQuery

I've also tried

Convert(smalldatetime, @pTimeStamp, 20)

as well as

CAST(@pTimeStamp AS smalldatetime)

but I only get other errors. I also tried declaring @pTimeStamp as varchar(50) and then converting but still got errors.

Doing something simple like:

DECLARE @pTimeStamp smalldatetime
SET @pTimeStamp = '2012-01-22 12:15:00'

Select *
From SampleTable
Where TimeStamp = @pTimeStamp

ran fine so I'm guessing it has to do with the dynamic SQL.

Please help....

Whenever I run into an issue like this I add a PRINT to my code for debug, to see what my dynamic sql looks like. — Jacob H
– Jacob H, Commented Jun 7, 2017 at 16:13
Why are you writing such a query? Why don't you use parameterized queries? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 7, 2017 at 16:14
Why are you using dynamic sql here in the first place? From what you posted there is no need for dynamic sql at all. And if you do, you should parameterize it. — Sean Lange
– Sean Lange, Commented Jun 7, 2017 at 16:14
Sorry, it's a code snippet and is part of a parameterized query. Everything about the query works, I only took out this part for simplicity sake as it is the only part that does not work. — Tilting Code
– Tilting Code, Commented Jun 7, 2017 at 16:29
That big string manipulation is NOT a parameterized query. You may be passing parameters to your code but then you are executing it with dynamic sql which completely ruins the safety of parameters. — Sean Lange
– Sean Lange, Commented Jun 7, 2017 at 16:31

Community · Accepted Answer · 2020-07-29 18:30:16Z

2

The only truly safe formats for date/time literals in SQL Server, at least for datetime and smalldatetime, are: YYYYMMDD and YYYY-MM-DDThh:mm:ss[.nnn] - Bad habits to kick : mis-handling date / range queries - Aaron Bertrand

You are already using sp_executesql, so why not take advantage of its parameters?

declare @pTimeStamp smalldatetime;
declare @params nvarchar(4000);
declare @sqlquery nvarchar(4000);

set @pTimeStamp = '2017-05-22T12:15:00';
set @params = N'@pTimeStamp smalldatetime';
set @sqlquery = N'
select *
from SampleTable
where TimeStamp = @pTimeStamp';

execute sp_executesql @sqlquery, @params, @pTimeStamp;

rextester demo: http://rextester.com/FVC44260

Dynamic sql reference:

edited Jul 29, 2020 at 18:30

CommunityBot

11 silver badge

answered Jun 7, 2017 at 16:36

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tilting Code Over a year ago

Eureka! Thank you SqlZim.... especially for the sample code. Looking up parameterized queries just made things difficult but your code made total sense out of it. The "T" in the middle of the value confuses me as the stored values to not have it and I did not include it in my parameter of the SP. I'll try to figure out what that is used for as I saw it used in my searches. Thanks again!

Xavier J · Accepted Answer · 2017-06-07 16:13:23Z

0

You need to embed the timestamp in single quotes. Try:

Set @SQLQuery = N'Select *' + 
' From SampleTable'  +
' Where TimeStamp = ''' + @pTimeStamp + ''''

answered Jun 7, 2017 at 16:13

Xavier J

4,7631 gold badge16 silver badges29 bronze badges

4 Comments

Panagiotis Kanavos Over a year ago

That's a bad idea. The OP needs ot use a parameter instead of string concatenation. It's not only that this allows SQL injection attacks, it also leads to conversion and parsing problems. It's a lot easier to write a correct query

Xavier J Over a year ago

@PanagiotisKanavos Bad? Not necessarily. The @pTimeStamp variable is of type smalldatetime. There's no opportunity for SQL injection. The statement would have to recompile each time it is executed. You got anything else?

Panagiotis Kanavos Over a year ago

Actually yes. First of all, if that's the actual statement, there's no need to use dynamic SQL. If the parameter comes from another source, both injection and conversion errors are possible. You still lose the parameterization benefits, generating redundant execution plans. You can't fix a bad query by adding quotes. Writing a correct query is easier, faster and more secure. Why cover up a bad query ?

Tilting Code Over a year ago

I used ' Where TimeStamp = ''' + @pTimeStamp + '''' and still get the error.

Collectives™ on Stack Overflow

SQL Server - Converting to smalldatetime in dynamic SQL Statement

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related