Hello Everyone and thanks for your help in advance. I am trying to write a stored procedure that will return records between two given dates. For example:

Select * From myTable Where OrderDate >= BegDate AND =< EndDate

With the intention of returning all records equal to or between the starting and ending dates. However, when I pass the same date to both the BegDate and EndDate paramteters, I receive no records. I am really not sure why this is the case. Any help on how to solve this would be greatly appreciated. Thanks.

Are you using SQL Server?

I could be wrong, but should you use the column name for the second clause as well?, e.g.:

Select * From myTable Where OrderDate >= BegDate AND OrderDate =< EndDate

Alternatively, you could use BETWEEN:

Select * From myTable Where OrderDate BETWEEN BegDate AND EndDate

Between is inclusive, so it matches dates equal to BegDate as well.

Cheers,

Imar



---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hi Imar,

Yes, I am using the same column name for each as you noted. I also tried the BETWEEN and it returned nothing as well. The SQL Server column is of datetime type.

I think this is one of those SQL Server date thingies again. If you take a look at what you exactly you're querying for, things become clearer.

If you query for something that is larger than or equal to, and smaller than or equal to, you end up with only equal to, right? (If it's larger, it can't be smaller at the same time). So, in effect, this is what you query for:

WHERE OrderDate = MyDate

I assume that the OrderDate in your SQL Server table is a date with time info as well (3/10/2004 5:15:39 PM, for example), and that MyDate is just a day, month and year, defaulting to 00:00:00 for the time part
.
To fix this, you'll need to convert your OrderDate to a varchar, and then convert it back to a date. This will cause OrderDate to have a time element of 00:00:00 as well, so your query will work. Use something like this:

CONVERT(datetime, CONVERT(varchar(10), YourDateHere, 111))

This first converts the date to a varchar of 10 characters, so you end up with something like: 2004/04/10 That varchar is then converted back into a date, resulting in 2004-03-10 00:00:00.000

There may be quicker ways to do the conversion, but at least this will get you what you need.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Right as always Imar. I actually solde it by appending "12:00:00 am" to the beginning date parameter and "12:59:59 pm" to the ending date before passing them to the SPROC. Seems to work fine.

As always, thanks for the help.