Juergen Buddy Hoffmann wrote:
>
> On Wed, Sep 01, 1999 at 12:50:25PM +0200, Martin Ramsch wrote:
> > On Wed, 1999-09-01 12:12:56 +0200, Juergen Buddy Hoffmann wrote:
> > > How can I get records that match the entered date during a period? for
> > > example:
> > >
> > > event-name beginning end
> > > search&destroy 1999-05-27 1999-06-10
> > >
> > > Date entered in the search engine
> > > 1999-05-30
> > >
> > > What is the correct select statement to retrieve the data?
> >
> > SELECT *
> > FROM yourtable
> > WHERE '1999-05-30' BETWEEN beginning AND end;
> >
> Hi everyone, this works just fine with specific Dates. But what if I only
> have the year and month?
> Following Statements and Output...
>
> mysql> select VAID,Start,Ende from Termine where '1999-05-27' BETWEEN Start AND
> Ende order by VAID;
> +------+------------+------------+
> | VAID | Start | Ende |
> +------+------------+------------+
> | 8 | 1999-04-01 | 1999-10-31 |
> | 13 | 1999-04-01 | 1999-10-31 |
> | 14 | 1999-04-01 | 1999-10-31 |
> | 37 | 1999-04-01 | 1999-10-31 |
> | 39 | 1999-04-01 | 1999-10-31 |
> | 40 | 1999-04-01 | 1999-10-31 |
> | 41 | 1999-04-01 | 1999-10-31 |
> | 46 | 1999-04-01 | 1999-10-31 |
> | 48 | 1999-05-03 | 1999-06-28 |
> | 55 | 1999-01-01 | 1999-12-31 |
< ... >
> and
>
> mysql> select VAID,Start,Ende from Termine where '1999-05' BETWEEN Start AND Ende
> order by VAID;
> +------+------------+------------+
> | VAID | Start | Ende |
> +------+------------+------------+
> | 8 | 1999-04-01 | 1999-10-31 |
> | 13 | 1999-04-01 | 1999-10-31 |
> | 14 | 1999-04-01 | 1999-10-31 |
> | 37 | 1999-04-01 | 1999-10-31 |
> | 39 | 1999-04-01 | 1999-10-31 |
> | 40 | 1999-04-01 | 1999-10-31 |
> | 41 | 1999-04-01 | 1999-10-31 |
> | 46 | 1999-04-01 | 1999-10-31 |
> | 55 | 1999-01-01 | 1999-12-31 |
< ... >
> Any help is highly appreciated. Thanks in advance and kind regards
>
> --
> Mit freundlichen Gruessen
> Juergen Hoffmann
Hi Juergen
The above is absolute correct!!!
The rows missing don't lay in your between specification.
Mysql will make '1999-05-01' out of your '1999-05' date.
So VAID=48 ('1999-05-03' till '1999-06-28') is an explicitely forbidden row.
If you want to get all out of year 1999 and month 5 you have to use:
SELECT
VAID
, Start
, Ende
FROM
Termine
WHERE
1999 BETWEEN YEAR( Start ) AND YEAR( Ende )
AND 5 BETWEEN MONTH( Start ) AND MONTH( Ende )
;
Tschau
Christian
PS: Sorry for the late answer, I was really busy.
