Need help with a DATE issue

[xwishmasterx]

I have 2 tables which both contains timestamp (same format). I am trying to do a count from one of the tables and need to do the count where the DATES are the same I have tried this:

 

WHERE DATE(teamwar_info.war_started) = DATE(vtp_tracking.action_date)

 

I am getting a result, but not the right one.

 

If I do like this:

 

DATE(teamwar_info.war_started) = curdate() 

 

It counts all values found.

 

How do I do a count where the date for teamwar_info.war_started and vtp_tracking.action_date are the same?

[Pikachu2000]

The = condition of the first query you posted is ambiguous. You'd need to do a subquery to get the right value.

[xwishmasterx]

ok what I am trying now is to trim the to timestamps so only the date is left. How do I trim the hour,min,sec?

 

("*:*:*", $row_rs_wartime2['action_date'])

 

 

[Pikachu2000]

SELECT DATE(action_date) AS act_date

[xwishmasterx]

Thanks pikachu2000 that did the trick with just getting the date.

Unfortunately this did help me as I thought. Goin back to previous post I still need:

 

WHERE DATE(teamwar_info.war_started) = DATE(vtp_tracking.action_date)

 

Is there a way I can combine them? I am amazed how difficult it is to just compare to timestamps...

[Pikachu2000]

You're going to nee to post the whole query, and a detailed description of what results you're after.

[xwishmasterx]

ok here is the code I would like to get to work first:

 

$sql_teamwarinfo = "SELECT count(vtp_tracking.teamid), vtp_tracking.action_date, teamwar_info.war_started
FROM vtp_tracking, teamwar_info
WHERE vtp_tracking.teamid=".$members_teamid." AND teamwar_info.war_started < vtp_tracking.action_date";
$rs_teamwarinfo = mysql_query( $sql_teamwarinfo );
$row_teamwarinfo = mysql_fetch_array($rs_teamwarinfo);

 

This part is the problem : "teamwar_info.war_started < vtp_tracking.action_date"

 

I want to count ONLY where the date is AFTER "teamwar_info.war_started"

(in the end I want to count the between the date of "teamwar_info.war_started" and 2 days ahead)

 

I tried doing as you posted earlier (DATE ...) AS atc_date, but that only returns the first date..

 

Any ideas?

Any ideas?