SQL Between command

[mike55]

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.

[mike55]

Ok, it seems that Between has problems when the datatime has a non-zero time value. According to Roy Harvey at http://www.thescripts.com/forum/thread535358.html "the end date has

not time and any rows with non-zero times for that date are outside

the range, and thus excluded".

 

Mike55.

[MrPaul]

BETWEEN statement quirks

 

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

 

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

 

:)

[Nerseus]

I've never seen a database treat them differently. Really?

I've 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 MrPaul's query won't work.

 

The best and easiest query I've 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