Hi Olaf,
Sorry for this misunderstanding.
Try something like this:
AND dayofyear(gebdatum) BETWEEN dayofyear(now()) AND
dayofyear(date_add(now(),interval 7 day))
OR (dayofyear(now()) > dayofyear(date_add(now(),interval 7 day)) AND
(dayofyear(gebdatum) < dayofyear(date_add(now(),interval 7 day)) OR
dayofyear(gebdatum) > dayofyear(now())))
The first part deals with normal situations that both now() and gebdatum are
within the same year.
The second part (after the first OR) deals with the situation that you are
in the end of december but also need the birthdays of the first days of
january. Dayofyear(gebdatum) will then be smaller than dayofyear(now()).
I tested it with a couple of days and it seems to work. However, there is
still one problem: when your working with leap-years dayofyear will be
different for for instance March 1st. This can be overcome by adding an
extra condition to the where clause.
Please check the use of < and >, I didn't pay much attention to that.
Hope this helps
--
Peter Sap
----- Original Message -----
From: "Olaf van Zandwijk" <[EMAIL PROTECTED]>
To: "Peter Sap" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, November 18, 2003 8:17 AM
Subject: Re: Birthday calendar
> Hi,
>
> This can be done using 3.23.49a, but it won't work, because the birthday
> year is somthing like 1981 and now() returns 2003. That is exactly the
> problem which I try to work out.
>
> \Olaf
>
> Peter Sap wrote:
>
> >Hi Olaf,
> >
> >try something like this:
> >
> >AND gebdatum between now() and date_add(now(),interval 7 day)
> >
> >However, I have no idea if this can be done with 3.23.49a.
> >
> >--
> >Peter Sap.
> >
> >----- Original Message -----
> >From: "Olaf van Zandwijk" <[EMAIL PROTECTED]>
> >To: <[EMAIL PROTECTED]>
> >Sent: Monday, November 17, 2003 5:39 PM
> >Subject: Birthday calendar
> >
> >
> >
> >
> >>Hello,
> >>
> >>I want to have a query that returns the names from people that are
> >>having their birthday one of the coming 7 days. I currently have a buggy
> >>query that deals with this, but that query fails when the birthday is
> >>the next month, and we're now in the last week of the month before.
> >>
> >>Can anyone give me a hint in the right direction to do this? I'm using
> >>MySQL 3.23.49a, and it's not (yet) possible to use MySQL 4
> >>
> >>Grtz,
> >>\Olaf
> >>
> >>SELECT month(gebdatum) AS maand,
> >>dayofmonth(gebdatum) AS dag,
> >>achternaam,
> >>CONCAT(roepnaam, ' ', COALESCE(tussenvoegsel, ' '), ' ', achternaam) AS
> >>
> >>
> >naam
> >
> >
> >>FROM persoon,
> >>WHERE YEAR(gebdatum) IS NOT NULL
> >>AND <This part of the query needs to be redone>
> >>AND CONCAT(roepnaam, ' ', COALESCE(tussenvoegsel, ' '), ' ', achternaam)
> >>IS NOT NULL
> >>ORDER BY maand,
> >>dag,
> >>achternaam
> >>
> >>--
> >> __XXX__
> >> (0-0)
> >>+--ooO--(_)--Ooo-------------------------------------------------------+
> >>| |
> >>| Olaf van Zandwijk |
> >>| |
> >>| ICQ# 30231605 |
> >>| PGP Public Key: http://www.vanzandwijk.net/pgp.txt |
> >>+----------------------------------------------------------------------+
> >>
> >>
> >
> >
> >
> >
> >
>
> --
> __XXX__
> (0-0)
> +--ooO--(_)--Ooo-------------------------------------------------------+
> | |
> | Olaf van Zandwijk |
> | |
> | ICQ# 30231605 |
> | PGP Public Key: http://www.vanzandwijk.net/pgp.txt |
> +----------------------------------------------------------------------+
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
