WPW07 wrote:
> Hello,
>
> I've got a PHP form that returns results from a MySQL table [tasks]
> based on three parameters:
> task_resource
> begin_date
> end_date
>
> Here is my code in the record set dialog box:
>
> SELECT *
> FROM tasks
> WHERE (task_resource = varName) AND (begin_date BETWEEN varBeginDate
> AND varEndDate OR end_date BETWEEN varBeginDate AND varEndDate)
> ORDER BY tasks.begin_date
>
> I thought it was working fine until I ran into a problem. The SQL
> statement doesn't work if I want to see only those tasks begun and
> completed on the same day. For example, I entered begin_date 02/22/08
> to end_date 02/22/08 because I only want to see tasks started AND
> completed on that date. But my SQL is set up (with the "OR") so that
> it's returning tasks with a begin_date of 02/21/08 and end_date of
> 02/22/08.
>
> How can I correct my SQL? Not sure how to do this if I take out the OR
> condition.
>
> Thank you.


Does your date field include the time?
DATE with no time = 2008-02-28 00:00:00
To get what you want you would need fromdate through fromdate+1 gives
you 2008-02-28 00:00:00 through 2008-02-29 00:00:00

mysql> select * from t1
-> ;
+------+---------------------+
| a1 | a2 |
+------+---------------------+
| 1 | 2008-02-28 22:33:43 |
| 2 | 2008-02-28 22:34:02 |
+------+---------------------+

mysql> select * from t1 where a2 between '2008-02-28' and '2008-02-28';
Empty set (0.14 sec)


mysql> select cast('2008-02-28' as datetime) from t1; <<this is
essentially what you are asking for in your current query!!

+--------------------------------+
| cast('2008-02-28' as datetime) |
+--------------------------------+
| 2008-02-28 00:00:00 |
| 2008-02-28 00:00:00 |
+--------------------------------+


mysql> select * from t1 where a2 between '2008-02-28' and '2008-02-29'
-> ;
+------+---------------------+
| a1 | a2 |
+------+---------------------+
| 1 | 2008-02-28 22:33:43 |
| 2 | 2008-02-28 22:34:02 |
+------+---------------------+
2 rows in set (0.00 sec)