Thanks for your help Michael. That looks like it should solve my
problem. I will test today..



On Feb 28, 11:45 pm, Michael Austin <maus...@firstdbasource.com>
wrote:
> 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)