SQL query with between for Dates fetches no records

rakesh17684 · New User Joined: 08 Oct 2006 Posts: 61 Location: San Diego

i have a SQL in which i fetch records based on a date range.
i use a 'between' in the where clause of the query. when the start date is greater than the end date in the query i get no rows but vice versa i get the records i want.

why is that ??

SELECT * FROM table1
WHERE date BETWEEN '2008-01-01' and '2008-02-20'; --->

i get the records

SELECT * FROM table1
WHERE date BETWEEN '2008-02-20' and '2008-01-01' ; --->

No records fetched

Craq Giegerich · Posted: Fri Jul 25, 2008 1:03 pm

If a date is >= '2008-02-20' it can not be <= '2008-01-01'!

ashimer · Posted: Fri Jul 25, 2008 2:44 pm

COL BETWEEN A AND B is equivalent to COL >= A AND COL <= B

dick scherrer · Posted: Fri Jul 25, 2008 8:10 pm

Hello,

Terry Heinze · Posted: Sat Jul 26, 2008 5:13 am

Don't confuse the square root of -1 with SQL, Dick.

vini_srcna · Posted: Mon Jul 28, 2008 6:17 pm

Thats how it is defined to work

chandan.inst · Posted: Fri Aug 01, 2008 11:20 am

It won't Display any row if you execute the query like
SELECT * FROM table1
WHERE date BETWEEN '2008-02-20' and '2008-01-01'

The generic example using between is like select.... where <col> BETWEEN 1st value and 2nd value

Because for BETWEEN function 1 the 1st value should be low as it selects the only those values whcih satisfies the criteria value >= 1st Value and value =< 2nd value and then fetches the records

In your case there will no such records will come as it will search for dates where Date >= '2008-02-20' and Date =< '2008-01-01'

Reagrds,
Chandan