OleDbType.Date as a parameter to a Sql Server stored proceedure

Hi all,
   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.Add("@StartingDate", OleDbType.Date);
selectCommand.Parameters["@StartingDate"].Value = startingDate   // Starting date is passed in as a DateTime variable
selectCommand.Parameters.Add("@EndingDate", OleDbType.Date);
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?

Thanks all.
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

ThaloxConnect With a Mentor Commented:

I think oledb will handle the format itself, and put it into the right format for the db.
have you tried to change the dates
(e.g. exec dateRangeSearchSp 'Oct 31, 2004 12:00:00 AM', 'Nov 12, 2004 12:00:00 AM')

maybe its just the "wrong" time...

Mohammed NasmanSoftware DeveloperCommented:
try DBDate instead of Date
edcAuthor Commented:
Hi mnasman,
   It gave the same result.
edcAuthor Commented:
Hi Thalox,
   Thanks for the reply.  All of the records have a date of between 11/5/2004 and 11/9/2004.  The I'm looking at the formatting that has been passed by OleDb to Sql Server via Sql Server's SQL Profiler, which shows me exactly what is being run.  It is coming up as described above.
edcAuthor Commented:
Hi Thalox,
   Well, it turns out I'm pretty much of a dumb-a**  I was passing startingDate to the SP for both parameters, and you were indeed right that my times were off.

All Courses

From novice to tech pro — start learning today.