List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:September 9 1999 5:00pm
Subject:Re: How to use the correct Date and Time Functions
View as plain text  
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.

Thread
How to use the correct Date and Time FunctionsJuergen Buddy Hoffmann1 Sep
  • Re: How to use the correct Date and Time FunctionsMartin Ramsch1 Sep
    • Re: How to use the correct Date and Time FunctionsJuergen Buddy Hoffmann1 Sep
    • Re: How to use the correct Date and Time FunctionsJuergen Buddy Hoffmann1 Sep
      • Re: How to use the correct Date and Time FunctionsMartin Ramsch14 Sep
  • Re: How to use the correct Date and Time FunctionsChristian Mack9 Sep
    • Re: How to use the correct Date and Time FunctionsMartin Ramsch14 Sep