OleDbType.Date as a parameter to a Sql Server stored proceedure
Posted on 2004-11-11
I need to pass to dates to a stored proceedure that will act as starting and ending dates for a search SP. We are using OleDb as the technology to connect to the database(s). We can not use SqlClient because we don't know what type of database will be on the back end. Right now we are developing against SQL Server 2000.
In a C# function we configure an OleDbCommand to add 2 parameters as follows:
selectCommand.Parameters["@StartingDate"].Value = startingDate // Starting date is passed in as a DateTime variable
selectCommand.Parameters["@EndingDate"].Value = endingDate // Ending date is passed in as a DateTime variable
Using SQL Profiler I can see that the stored proceedure is being called as:
exec dateRangeSearchSp 'Nov 1, 2004 12:00:00 AM', 'Nov 11, 2004 12:00:00 AM'
When we execute this in SQL Query Analyser I get zero rows returned. When I alter the SQL to
exec dateRangeSearchSp '11/1/2004', '11/11/2004' I get the 3 rows I expect from the database. So it looks like it's a formatting problem, but how do I get OleDb to output in the format I need?