SQL Between command

mike55

mike55

Well-known member
Joined
Mar 26, 2004
Messages
726
Location
Ireland
I am using a between command in an sql statement. If I go to my SQL server manager and enter the command directly, Select * From members where DOB between 08/28/2007 and 08/28/2007, I get back all those records that are have the DOB 08/28/2007.

However if I run the same statement from my code, I cannot get any of the values for the DOB 08/28/2007, if I change the between command from the 08/27/2007 and 08/29/2007 I get the records that I am looking for.

Any suggestions in regards to the difference? Alternatively, is their some inclusive command I can include?

Mike55.
 
BETWEEN statement quirks

Different database engines treat the BETWEEN statement differently - some exclusive, some inclusive. Theres no way to control this behaviour explicitly or otherwise. However, the following should work in all cases:

Code: 
SELECT * FROM members WHERE (DOB >= 08/28/2007) AND (DOB <= 08/28/2007)

:)
 
Ive never seen a database treat them differently. Really?
Ive always seen BETWEEN treat things inclusively. So BETWEEN 1 and always picks up 1, 2, and 3.

At least as far as SQL Server is concerned, there is no column that only holds a date. Everything has a time. Even if you insert 8/28/2007, SQL will tag on 12:00:00AM - exactly midnight.

If you have two rows on the same day, different times, then even MrPauls query wont work.

The best and easiest query Ive used is one that uses the following day, as in:
WHERE column >= @DateParam AND column < @DateParamPlusOneDay

For example, to find all rows with a date on 8/28/2007 regardless of the time:
WHERE column >= 8/28/2007 AND column < 8/29/2007

You *could* use BETWEEN, but the 2nd value in the expression would have to include the last possible second of time, such as:
WHERE column BETWEEN 8/28/2007 AND 8/28/2007 11:59:59.999PM

-ners
 
Back
Top