QUERY date

[johnrb87]

Hi everyone

 

I have a database with table containing my news

 

it has a `start_date` field and a `end_date` field and I store dates as YYYY-MM-DD

 

I have a PHP script on my webpage which I have a date set on it, such as

 

$date = '2010-10-23'

 

What QUERY would I need to run to return all rows which the date defined falls between the start and end date

 

Any help would be great

 

I tried

 

WHERE `start_date` >= '2010-09-13' AND `end_date` <= '2010-09-13'

 

but that doesn't seem to work

[the182guy]

Try this

 

$date = '2010-10-23';
$sql = "SELECT * FROM mytable WHERE DATE_FORMAT(`start_date`, '%Y%m%d') >= DATE_FORMAT('$date', '%Y%m%d') AND DATE_FORMAT(`end_date`, '%Y%m%d') <= DATE_FORMAT('$date', '%Y%m%d')";

[gizmola]

What is the type of the start_date and end_date columns?  Is it a varchar?  You should really use a DATE column for those, and then your query would work, not to mention perform well even if the table grows large.  Storing those dates as varchars or chars is not the way to go, and you already have them in the proper format for mysql to convert them (although make a backup of the table if you plan to ALTER it first, just in case).

[johnrb87]

Yep they are both DATE type fields

 

I tried

 

SELECT * FROM `news` WHERE DATE_FORMAT(`start_date`, '%Y%m%d') >= DATE_FORMAT('2010-09-10', '%Y%m%d') AND DATE_FORMAT(`end_date`, '%Y%m%d') <= DATE_FORMAT('2010-09-10', '%Y%m%d')

 

but it returns 0

[the182guy]

Actually it should be something like this

$date = '2010-10-23';
$sql = "SELECT * FROM mytable WHERE DATE_FORMAT('$date', '%Y%m%d') >= DATE_FORMAT(`start_date`, '%Y%m%d') AND DATE_FORMAT('$date', '%Y%m%d') <= DATE_FORMAT(`end_date`, '%Y%m%d')";

[PFMaBiSmAd]

Passing every piece of data through the DATE_FORMAT() function in the WHERE clause is a query killer, especially since one of the main points of a DATE data type is you can compare DATE values directly.

 

Your comparison logic is backwards. Take your first query and put in some actual/likely values for the start/end dates that you would expect the query to match (this is how you debug/design logic, you play computer, use some pencil and paper, put in some values and work out if the logic is correct.) Given that the start_date is less than the end_date, the query would look like -

 

WHERE `2010-09-01` >= '2010-09-13' AND `2010-09-14` <= '2010-09-13'

 

How is that ever going to be true (except for the case where all the values were 2010-09-13 and both = comparisons match.)

 

You would want -

 

WHERE `2010-09-01` <= '2010-09-13' AND `2010-09-14` >= '2010-09-13'

 

or more simply using the BETWEEN comparison (with the php variable and actual column names back in) -

 

WHERE '$date' BETWEEN start_date AND end_date

[johnrb87]

thanks everyone, you were all a great help and my problem is now solved.

 

Thanks