Dynamic StoredProc: Error from datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic StoredProc: Error from datetime

Author

Topic

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-09-22 : 19:09:31

Hello. I'm running a dynamic stored procedure and it's giving me an error because of the dates I'm trying to insert into the SQL statement. I expect I'm missing something really easy but haven't been able to come across the solution yet. I would appreciate any help. Thanks.

---------------------
Error from Query Analyzer:

Syntax error converting datetime from character string.

---------------------
Stored procedure:

CREATE PROCEDURE [sp_storedproc]
(@StartDate datetime,
@EndDate datetime,
@WhereStatement varchar(1000))
AS
DECLARE @SQLStatement varchar(2000)
SELECT @SQLStatement = "SELECT ... FROM ... WHERE (Table.Date >= '" + @StartDate + "') AND (Table.Date <= '" + @EndDate + "')" + @WhereStatement + "GROUP BY ... ORDER BY ... "
EXEC(@SQLStatement)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-22 : 19:22:38

WHERE (Table.Date >= '" + convert(varchar(8),@StartDate,112) + "')

And don't use double quotes ass string delimitters.
SELECT @SQLStatement = 'SELECT ... FROM ... WHERE (Table.Date >= ''' + convert(varchar(8),@StartDate,112) + ''')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-22 : 19:24:29

You'll need to format the date as a string/varchar before you can append it to your dynamic sql statement:

SELECT @SQLStatement = "SELECT ... FROM ... WHERE (Table.Date >= ''' Convert(varchar, @StartDate, 101) + ''')

Also - swap your double quotes for double single quotes, as I've done here.

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-09-22 : 19:59:51

I tried both of the convert functions you two listed and they both give me the same (new) error.

Error: Syntax error converting character string to smalldatetime data type.

I have tried a couple different date formats, but the result is the same.

@StartDate = '01/01/2000 12:00:00'
&
@StartDate = '01/01/2000'

Thanks again for the help.

Huligan

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-22 : 20:26:48

Doesn't matter what you put in @StartDate - it's a datetime so as long what you put in is successfull then it should work.
I would recommend always using yyyymmdd format when using character dates as they always convert impliciely and it is the only format that does.
If you changed both the date compares in that statement then they won't cause a problem - not ethe convert I gave you removes the time but you can add that later.

Instead of execing the sql select it then you can look at it and see what's wrong - and also execute parts of the string to eliminate things.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-22 : 20:37:05

Their's Got to be a better way...

Set @EndDate =DateName(yyyy,GetDate()) +'/' + Cast(DatePart(m,GetDate()) as varchar(2)) + '/' +DateName(dd,GetDate())
Print @EndDate
SELECT @SQLStatement = 'SELECT Top 10 * FROM MGT_ULTIMATES WHERE Updated_Date >= ' + @EndDate

RESULT
SELECT Top 10 * FROM MGT_ULTIMATES WHERE Updated_Date >= 2004/9/22

Surf On Dude!

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-22 : 20:38:15

Sorry just got the Convert Post

Surf On Dude!

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-22 : 20:42:29

Missing Piece: Varchar Not DateTime
Declare @StartDate VarChar(20), @EndDate VarChar(20)

Surf On Dude!

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-09-23 : 07:31:31

I figured it out! I did a Print(@SQLStatement) to see what the SQL string looked it. My SQL is a little weak and didn't know there was a Response.Write equivalent in SQL. Anyway, the new error was popping up because the Convert function was being written to the string literally. Meaning, there was no date being inserted into the SQL string, Convert() was inserted. I replaced the single quotes pairs with 1 double quote. That was the trick.

My question is this. Both timmy and nr specifically mentioned to swap my double quotes with 2 single quotes. What was the reason behind this suggestion? I would like to know the reason in case I'm missing something or I might run into a situation that calls for the swap. Keep in mind, I'm running my dynamic stored procedure in query analyzer.

Thanks again for everyone's help. I really appreciate it.

Huligan

slacker
Posting Yak Master

115 Posts

Posted - 2004-09-23 : 14:45:24

Always use sp_executesql instead of EXEC when running dynamic queries.. Sql server will create an execution plan for it. Plus it allows you to use parameters in your query. Also you gotta fully qualify your object names or it wont optimize.. from the books online


Note  If object names in the statement string are not fully qualified, the execution plan is not reused.

oops... I forgot to put the N. sp_executesql only works with unicode strings... so prefix the strings with N


CREATE PROCEDURE [sp_storedproc]
(@StartDate datetime,
@EndDate datetime,
@WhereStatement varchar(1000))
AS
DECLARE @SQLStatement varchar(2000)
DECLARE @Params       varchar(2000)
SELECT @SQLStatement = 
'SELECT ... FROM ... WHERE (Table.Date >= @StartDate AND (Table.Date <= @EndDate ' + @WhereStatement + 'GROUP BY ... ORDER BY ... '
SET @Params = '@StartDate DateTime'
SET @Params = @Params + ',@EndDate DateTime'

EXECUTE sp_executesql 
@SQLStatement, 
@Params,
@StartDate = @StartDate,
@EndDate = @EndDate

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-23 : 14:45:59

Because a single quote is a string delimitter.
If quoted identifiers are on then a double quote is an identifier delimitter (but you should use [] anyway) otherwise it should have no special meaning.

Odd because my first suggestion should have placed the converted value in the string not the convert statement.
SELECT @SQLStatement = 'SELECT ... FROM ... WHERE (Table.Date >= ''' + convert(varchar(8),@StartDate,112) + ''')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources