Help with SQL Syntax

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.

On 28 Feb, 15:18, WPW07 <wwisnie...@gmail.com> 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.

Could you possibly use an internationally recognised date format when
discusing things here, it makes life much simpler.

Now, you have explained that you want to be able to see only tasks
begun and completed on the same date. This is of course trivial:
WHERE begindate = x AND enddate = x

But you haven't explained whatelse this expression will be required to
produce.

On Feb 28, 10:51 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 28 Feb, 15:18, WPW07 <wwisnie...@gmail.com> 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.
>
Thank you for your reply. I did try WHERE begindate = x AND enddate =
x But the problem is that if there isn't task that meets those exact
data parameters nothing gets returned, where as if the begin date is
between x and y and the end date is between x and y, results do get
returned.

> > How can I correct my SQL? Not sure how to do this if I take out the OR
> > condition.
>
> > Thank you.
>
> Could you possibly use an internationally recognised date format when
> discusing things here, it makes life much simpler.
>
> Now, you have explained that you want to be able to see only tasks
> begun and completed on the same date. This is of course trivial:
> WHERE begindate = x AND enddate = x
>
> But you haven't explained whatelse this expression will be required to
> produce.

On Feb 28, 7:51 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 28 Feb, 15:18, WPW07 <wwisnie...@gmail.com> wrote:
>
>
> > 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.


>
> Could you possibly use an internationally recognised date format when
> discusing things here, it makes life much simpler.
>

Oh come on, Cap'n!

OBVIOUSLY that's August 2nd, 2022!

WPW07 wrote:
> On Feb 28, 10:51 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>> On 28 Feb, 15:18, WPW07 <wwisnie...@gmail.com> wrote:
>> > 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.
>>
> Thank you for your reply. I did try WHERE begindate = x AND enddate =
> x But the problem is that if there isn't task that meets those exact
> data parameters nothing gets returned, where as if the begin date is
> between x and y and the end date is between x and y, results do get
> returned.
If you read my post properly you will see that I acknowledged that this
obviously wasn't going to be the answer. I also pointed out that since "you
haven't explained what else this expression will be required to produce.",
we cannot advise you of what it should be.

If you would care to explain your requirements we will endeavour to help.

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)

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)