COUNT from one date to another

[JasonORDSW]

I am running a members profile ssytem.. and I am trying to get a page to display the total of NEW members since a individual last logged on.

 

My current code is:

 

$query_newmembers = mysql_query("SELECT COUNT(*) FROM members WHERE TO_DAYS(stamp_approved)-TO_DAYS(\'$stamp_lastlogin2\')>0 AND status=\'A\' AND level IN (2,3,4)");

 

however it does not seem to be displaying the PROPER number of new members since a person last logged on (lastlogin2)

 

Can anyone help?

 

Thanks!

 

Jason[/code]

[shivabharat]

Whats the filed type of \"stamp_approved\" is that timestamp/datetime or date?

 

This is the synaxt for TO_DAYS

 

SELECT TO_DAYS(\'2003-10-01\');

[JasonORDSW]

The Field Type for stamp approved and for lastlogin2 is datetime

 

I thank you in advance for your help!

 

Jason

[shivabharat]

If you have the filed type as time stamp you may need to extract the date alone used

 

SELECT COUNT(*) FROM members WHERE TO_DAYS(substring(<field_name>,1,11)) -TO_DAYS(\'$stamp_lastlogin2\') )>0 AND status=\'A\' AND level IN (2,3,4)"); 

 

ensure that \"$stamp_lastlogin2\" is in date format YYYY-MM-DD

[JasonORDSW]

Both the stamp approved and the lastlogin2 are datetime fields.. I wanted to kep it this way.. so it has a more ACCURATE reporting for when you do certain functions on the site.

 

Looks like I will have to extract the date ONLY for both fields.. THANKS for your help!

 

Any other suggestions.. please pass them along!

 

Jason