Date range not working

Mandakins

I'm having problems pulling records from my DB by date range.

$stamp = time() - 2592000;
$yesterday = date("Y-m-d",$stamp);
$today = date("Y-m-d");

$sql12 = "SELECT * FROM news where newsdate >= $yesterday AND newsdate <= $today ";

Yeilds this -> SELECT * FROM news where newsdate >= 2003-08-19 AND newsdate <= 2003-09-18

There are 2 records in my DB - one is 2003-08-20 and one is 2003-09-17.
The search is returning 0 rows. ???

I have also tried:

$sql12 = "SELECT * FROM news where newsdate BETWEEN $yesterday AND $today ";

Same thing, no rows returned.

What's the default format for a date field in mySQL? Maybe my month and day are reversed? Any ideas?

Sxooter

= 2003-08-19 AND newsdate <= 2003-09-18

Think about it.

How can a date be both GREATER than 2003-08-19 AND LESS than 2003-09-18 at the same time? I think you've got your dates reversed here.

Mandakins

Well, the idea is to get any record where the date falls within the last 30 days, so if I'm asking for anything greater than August 19th and less than September 18th, I thought that meant give me anything after the 19th of August and before the 18th of September.

Regardless of that syntax, getting the dates BETWEEN the 19th of August and the 18th of September should work no? So what's wrong with that?

rgermain

The way it looks to me is that you have a date type field and in order to search this you must have your select statement look like the following:

$newDate = "20030918";
$oldDate = "200030312";

$query = " SELECT * FROM news where newsdate >= $newDate AND newsdate <= $newDate";

You exclude the " - " between the year and month and the month and day. I had the same problem.

Not so sure on the syntax of the select statement above, but it was just to show you the way the date should be formatted

Hope that helped.

Mandakins

That was it! Thanks so much!

Sxooter

Also, look at between:

select * from table where date between '$start' and '$end';

Much easier syntax.