Seeing if a date is between two dates in a table...

[djanim8]

ok I have 2 fields in my table, (date1 and date2) with date1 = 2007-08-01 and date2 = 2007-09-30

 

I'm trying to run this sql statement:

 

SELECT * FROM discounts WHERE discType = 'Date Range' AND date1 >= '2007-09-15' AND date2 <= '2007-09-15'

 

and it won't return any rows...

 

is there a way to see if a date is between 2 days in a table? (btw.. 'Date Range' is valid.. so thats not it)

[AndyB]

In your example, date1 is 2007-08-01 so the SELECT query should return zero rows.

 

Take a look at MySQL BETWEEN - http://www.webdevelopersnotes.com/tutorials/sql/tutorial_mysql_in_and_between.php3

[djanim8]

nothing in there does what I need...

 

it should work if its greater than or equal to (>=) date1 and less than or equal to (<=) date2

 

why doesn't that return anything?

[AndyB]

ok I have 2 fields in my table, (date1 and date2) with date1 = 2007-08-01 and date2 = 2007-09-30

 

I'm trying to run this sql statement:

 

SELECT * FROM discounts WHERE discType = 'Date Range' AND date1 >= '2007-09-15' AND date2 <= '2007-09-15'

 

and it won't return any rows...

 

if date1 = 2007-08-01 there will be no records where date1>=2007-09-15.

if date2 = 2007-09-30 there will be no records where date2<=2007-09-15

I would expect your query to return zero rows.

 

I assume I've failed to understand what you want to happen.

 

Your example seems at odds with trying to determine if a date lies BETWEEN two values (and BETWEEN was the subject of the link I gave)