List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 14 1999 1:42am
Subject:Re: How to use the correct Date and Time Functions
View as plain text  
On Wed, 1999-09-01 14:00:55 +0200, Jürgen Buddy Hoffmann wrote:
> > > 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?

You have to think about your problem:
a) If the user enters only a year or year-month, then you wrote you
   want this to be the _period_ from the first to the last day of this
   year or month.  And you want to select events that are entirely
   within this period,
   i.e. where periodfrom<=beginning and end<=periodto

b) If the user enters a full date, i.e. specifies a certain day, then
   you want the logic to be vice versa and want to select events that
   cover this day,
   i.e. where beginning<=theday and theday<=end

So you really have to do different SQL queries depending on the type
of input ...

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
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