passing date ranges to SPROC - SQL Server Forums

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)

passing date ranges to SPROC

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-06-03 : 22:46:53

I'm using dynamic sql in a sql server 7.0 sproc to process an ASP search form. One set of search criteria is a date range, i.e "date between date1 and date2.

I'm passing the dates to the sproc as char and then converting them to datetime fields in the sproc as below:

DECLARE @DateRange1_converted datetime

SET @DateRange1_converted=CONVERT(datetime, @DateRange1)

DECLARE @DateRange2_converted datetime

SET @DateRange2_converted=CONVERT(datetime, @DateRange2)

the problem arises in the dynamic sql code when the date range parameters are passed in. Here's the offending piece of code:

SELECT @SQL = @SQL + ' AND first_Date BETWEEN ' + @DateRange1_converted + ' AND ' + @DateRange2_converted

the asp error is:

"syntax error converting datetime from character string"

I'm having trouble coming up with the proper syntax -- how many single quotes and where do they go?

thx

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-03 : 23:35:16

You can use implicit conversion for dates.

If the dates are passed in the format 'dd-mmm-yyyy', you can insert them directly into your statements.
All you'll need to do is ensure that the date strings are validated before you pass them.

Alternatively, you can pass datetime's directly into your stored procedures. You will just need to ensure that the ADO datatype is correct (assuming you're building parameters).

Tim

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-06-05 : 01:42:10

Any standard date format should convert implicitly just fine. I tend to prefer YYYYMMDD for clarity. And if you're not comfortable counting all your quotation marks to make sure you've got them lined up right, consider using the QUOTENAME function around your variables.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources