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

Posted on 2004-11-11
Last Modified: 2008-03-03
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.
Question by:edc
    LVL 22

    Expert Comment

    try DBDate instead of Date
    LVL 1

    Author Comment

    Hi mnasman,
       It gave the same result.
    LVL 9

    Accepted Solution


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

    LVL 1

    Author Comment

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

    Author Comment

    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.


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
    Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
    This video discusses moving either the default database or any database to a new volume.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now