MySQL Select And Count Date Range: Doesn't work crossing the month barrier

Can you please tell me why this works:

$customer_data_date14daysAgo = mysql_query("SELECT COUNT(*) AS count 
FROM tableName WHERE datetime BETWEEN '$date14daysAgo%' and 
'$dateToday%' ") or die(mysql_error()); 

But this doesn't?

$customer_data_date30daysAgo = mysql_query("SELECT COUNT(*) AS count 
FROM tableName WHERE datetime BETWEEN '$date30daysAgo%' and 
'$dateToday%' ") or die(mysql_error());

PHP:

$dateToday = date ( 'Y-M-d', strtotime ( '-0 day' . $date ) );   
$date14daysAgo = date ( 开发者_JAVA百科'Y-M-d', strtotime ( '-14 day' . $date ) );   
$date30daysAgo = date ( 'Y-M-d', strtotime ( '-1 month' . $date ) );   

$dateToday = 2010-Oct-28

$date14daysAgo = 2010-Oct-21

$date30daysAgo = 2010-Sep-28

The only difference is that the second query spans the Sep - Oct barrier.

If I set the date manually to 2010-Oct-01 until today - it works

But if its 2010-Sep-30 until today - it stops working

Thank you!

if you want search data one month ago until the current date, perhaps this can help:

SELECT COUNT(*) AS count FROM table 
WHERE date BETWEEN DATE_SUB(CURRENT_DATE(),INTERVAL 1 MONTH) AND CURRENT_DATE()

and if you want to show 1 month ago( the current date not included), you can use:

SELECT COUNT(*) AS count FROM table 
WHERE date <= DATE_SUB(CURRENT_DATE(),INTERVAL 1 MONTH)

but if i'm not misunderstand.

by following your question, try like:

$customer_data_date30daysAgo = mysql_query("SELECT COUNT(*) AS count 
FROM tableName WHERE datetime BETWEEN '".$date30daysAgo."%' and 
'".$dateToday."%' ") or die(mysql_error());

MySQL doesn't like being given dates with month names (e.g. "2010-Oct-28"). The recommended format is all numeric like this: 2010-10-28.

So when you call the php date function, ask for it in that format:

$dateToday = date ( 'Y-m-d', strtotime ( '-0 day' . $date ) );   
$date14daysAgo = date ( 'Y-m-d', strtotime ( '-14 day' . $date ) );   
$date30daysAgo = date ( 'Y-m-d', strtotime ( '-1 month' . $date ) ); 

That will give these strings:
$dateToday = 2010-10-28
$date14daysAgo = 2010-10-21
$date30daysAgo = 2010-10-28

And that should make your SQL work better.

just wrote an article how to work with dates. it includes code samples for MySQL as well:

http://use-the-index-luke.com/sql/where-clause/obfuscation/dates