On 31 Jan, 10:00, "Luuk" <l...@invalid.lan> wrote:
> "Captain Paralytic" <paul_laut...@yahoo.com> schreef in berichtnews:9a68a4dd-07df-4e0d-89af-12c851ca9177@q21g2000hsa.googlegroups.com...
>
>
>
> > On 31 Jan, 09:01, "Luuk" <l...@invalid.lan> wrote:
> >> "Paul Lautman" <paul.laut...@btinternet.com> schreef in
> >> berichtnews:60bvnqF1msefuU1@mid.individual.net...
>
> >> > Carolyn Marenger wrote:
> >> >> How do I perform a select statement that would locate all records in
> >> >> which the day and month are in the next say 30 days regardless of the
> >> >> year. Basically an upcoming birthday/anniversary list.
>
> >> >> Thanks, Carolyn
>
> >> > I'm sure there's an easier way to do this, but here at least is one
> >> > method. Supposing the date field is anniversary:
>
> >> > SELECT
> >> > *
> >> > WHERE
> >> > DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(anniversary),'-',DAY(anniversary)),NOW())
> >> > < 30
>
> >> select datediff(now(),'2008-03-31');
> >> returns
> >> -60
>
> >> so,
> >> SELECT
> >> *
> >> WHERE
> >> ABS(DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(anniversary),'-',DAY(anniversary)),NOW())
> >> )
> >> < 30
>
> >> might work better...
>
> > Since the query has NOW() in the second position, your query with
> > NOW() in the first position cannot occur (parameters do not swap
> > positions all by themselves).
>
> ok, but
> select datediff('2007-12-01',now());
> also returns a negative number
>
> so by next month, when now is i.e. 2008-02-20
> this: select datediff('2008-01-01',now());
> will also return a negative number...

True, I KNEW you were on to something, I just realised that what you
had said was not it, but I didn't suss what it actually was.

So the where needs to be
WHERE
DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(anniversary),'-',DAY(anniversary)),NOW())
BETWEEN 0 AND 30

Thanks for that Luuk.

Captain Paralytic wrote:
> On 31 Jan, 10:00, "Luuk" <l...@invalid.lan> wrote:
>> "Captain Paralytic" <paul_laut...@yahoo.com> schreef in berichtnews:9a68a4dd-07df-4e0d-89af-12c851ca9177@q21g2000hsa.googlegroups.com...
>>
>>
>>
>>> On 31 Jan, 09:01, "Luuk" <l...@invalid.lan> wrote:
>>>> "Paul Lautman" <paul.laut...@btinternet.com> schreef in
>>>> berichtnews:60bvnqF1msefuU1@mid.individual.net...
>>>>> Carolyn Marenger wrote:
>>>>>> How do I perform a select statement that would locate all records in
>>>>>> which the day and month are in the next say 30 days regardless of the
>>>>>> year. Basically an upcoming birthday/anniversary list.
>>>>>> Thanks, Carolyn
>>>>> I'm sure there's an easier way to do this, but here at least is one
>>>>> method. Supposing the date field is anniversary:
>>>>> SELECT
>>>>> *
>>>>> WHERE
>>>>> DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(anniversary),'-',DAY(anniversary)),NOW())
>>>>> < 30
>>>> select datediff(now(),'2008-03-31');
>>>> returns
>>>> -60
>>>> so,
>>>> SELECT
>>>> *
>>>> WHERE
>>>> ABS(DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(anniversary),'-',DAY(anniversary)),NOW())
>>>> )
>>>> < 30
>>>> might work better...
>>> Since the query has NOW() in the second position, your query with
>>> NOW() in the first position cannot occur (parameters do not swap
>>> positions all by themselves).
>> ok, but
>> select datediff('2007-12-01',now());
>> also returns a negative number
>>
>> so by next month, when now is i.e. 2008-02-20
>> this: select datediff('2008-01-01',now());
>> will also return a negative number...
>
> True, I KNEW you were on to something, I just realised that what you
> had said was not it, but I didn't suss what it actually was.
>
> So the where needs to be
> WHERE
> DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(anniversary),'-',DAY(anniversary)),NOW())
> BETWEEN 0 AND 30
>
> Thanks for that Luuk.

Thanks to you and Luuk for your assistance with this. It works, now I
just have to write the PHP application to use the query.

Carolyn

Carolyn Marenger wrote:
> How do I perform a select statement that would locate all records in
> which the day and month are in the next say 30 days regardless of the
> year. Basically an upcoming birthday/anniversary list.
>
> Thanks, Carolyn

The following command worked for me. Thanks to Luuk and Captain
Paralytic for their debate in developing it.

SELECT description, eventDate
FROM dates
WHERE
DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(eventDate),'-',DAY(eventDate)),NOW())
BETWEEN 0 AND 60

On Thu, 31 Jan 2008 12:21:14 +0100, Carolyn Marenger <cajunk@marenger.com>
wrote:

> Carolyn Marenger wrote:
>> How do I perform a select statement that would locate all records in
>> which the day and month are in the next say 30 days regardless of the
>> year. Basically an upcoming birthday/anniversary list.
>> Thanks, Carolyn
>
> The following command worked for me. Thanks to Luuk and Captain
> Paralytic for their debate in developing it.
>
> SELECT description, eventDate
> FROM dates
> WHERE
> DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(eventDate),'-',DAY(eventDate)),NOW())
> BETWEEN 0 AND 60

Let's say today is 2007-12-05, date to check is 1982-01-16...

mysql> select
DATEDIFF(CONCAT(YEAR('2007-12-05'),'-',MONTH('1982-01-16'),'-',DAY('1982-01-16')),'2007-12-05');

+--------+
| -323 |
+--------+


SELECT
description,
eventDate,
DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(eventDate),'-',DAY(eventDate)),NOW())
as checkdays.
FROM dates
HAVING checkdays BETWEEN 0 AND 60
OR checkdays BETWEEN -365 AND (-365 + 60)

Now all we need is a statement to check for a leap year....
--
Rik Wasmus

Rik Wasmus wrote:
> On Thu, 31 Jan 2008 12:21:14 +0100, Carolyn Marenger
> <cajunk@marenger.com> wrote:
>
>> Carolyn Marenger wrote:
>>> How do I perform a select statement that would locate all records in
>>> which the day and month are in the next say 30 days regardless of the
>>> year. Basically an upcoming birthday/anniversary list.
>>> Thanks, Carolyn
>>
>> The following command worked for me. Thanks to Luuk and Captain
>> Paralytic for their debate in developing it.
>>
>> SELECT description, eventDate
>> FROM dates
>> WHERE
>> DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(eventDate),'-',DAY(eventDate)),NOW())
>> BETWEEN 0 AND 60
>
> Let's say today is 2007-12-05, date to check is 1982-01-16...
>
> mysql> select
> DATEDIFF(CONCAT(YEAR('2007-12-05'),'-',MONTH('1982-01-16'),'-',DAY('1982-01-16')),'2007-12-05');
>
>
> +--------+
> | -323 |
> +--------+
>
>
> SELECT
> description,
> eventDate,
> DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(eventDate),'-',DAY(eventDate)),NOW())
> as checkdays.
> FROM dates
> HAVING checkdays BETWEEN 0 AND 60
> OR checkdays BETWEEN -365 AND (-365 + 60)
>
> Now all we need is a statement to check for a leap year....

Good point. Thanks!

Carolyn

"Carolyn Marenger" <cajunk@marenger.com> schreef in bericht
news:77014$47a1adcc$cf70133e$440@PRIMUS.CA...
> Carolyn Marenger wrote:
>> How do I perform a select statement that would locate all records in
>> which the day and month are in the next say 30 days regardless of the
>> year. Basically an upcoming birthday/anniversary list.
>>
>> Thanks, Carolyn
>
> The following command worked for me. Thanks to Luuk and Captain Paralytic
> for their debate in developing it.
>
> SELECT description, eventDate
> FROM dates
> WHERE
> DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(eventDate),'-',DAY(eventDate)),NOW())
> BETWEEN 0 AND 60

one day, it wil be i.e 1st december, and someone has his/her birtday on i.e.
1st january

your statement fives (with dates filled in):
SELECT description, eventDate
FROM dates
WHERE
DATEDIFF(CONCAT('2008-01-01'), '2008-12-01')
BETWEEN 0 AND 60

so, will not select this person..

"Luuk" <luuk@invalid.lan> schreef in bericht
news:7rs875-ifb.ln1@leafnode.a62-251-88-195.adsl.xs4all.nl...
>
> "Carolyn Marenger" <cajunk@marenger.com> schreef in bericht
> news:77014$47a1adcc$cf70133e$440@PRIMUS.CA...
>> Carolyn Marenger wrote:
>>> How do I perform a select statement that would locate all records in
>>> which the day and month are in the next say 30 days regardless of the
>>> year. Basically an upcoming birthday/anniversary list.
>>>
>>> Thanks, Carolyn
>>
>> The following command worked for me. Thanks to Luuk and Captain
>> Paralytic for their debate in developing it.
>>
>> SELECT description, eventDate
>> FROM dates
>> WHERE
>> DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(eventDate),'-',DAY(eventDate)),NOW())
>> BETWEEN 0 AND 60
>
> one day, it wil be i.e 1st december, and someone has his/her birtday on
> i.e. 1st january
>
> your statement fives (with dates filled in):
> SELECT description, eventDate
> FROM dates
> WHERE
> DATEDIFF(CONCAT('2008-01-01'), '2008-12-01')
> BETWEEN 0 AND 60
>
> so, will not select this person..
>
>
>
>

thanks Rik, you where quicker... ;-)