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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mohammed NasmanSoftware DeveloperCommented:
try DBDate instead of Date
edcAuthor Commented:
Hi mnasman,
   It gave the same result.

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...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.