Now thats something I didn't know about, thanks for that!
Regards,
Sharon Cowling
> -----Original Message-----
> From: Josh Berkus [mailto:josh@agliodbs.com]
> Sent: Thursday, 27 June 2002 15:56
> To: Sharon Cowling; Pgsql-Novice (E-mail)
> Subject: Re: [NOVICE] Date Question
>
>
>
> Sharon,
>
> > I have a query that is not returning the desired results.
> I want the
> results to be a list of permit_id's held between 2 dates, but
> the results I'm
> getting is only those permit_id's that start on date_from
> ('27-06-02') and
> finish on date_to ('11-07-02'), I also want the permit_id's
> returned for
> those permits that are held within that time period, for
> example a permit
> that starts on 20-06-02 and finishes on 04-07-02. I've tried
> all sorts of
> variations but I'm still not getting the right results,
> anyone have any
> ideas?
> >
> > SELECT permit_id
> > FROM faps_permit
> > WHERE person_id = (select person_id from person where
> person_id = 100)
> > AND cancel_permit is null
> > AND location = 'Kent Forest'
> > AND ( (date_from >= '27-06-02' AND date_to <= '11-07-02')
> > OR (date_from <= '27-06-02' AND date_to >= '11-07-02') )
> > ORDER BY permit_id ;
>
> According to the conditions above, you'll be getting any
> permit_id where from
> and to are both between the two dates, or from and to are
> both outside the
> two dates. Not what you want.
>
> Actually, what you want is the OVERLAPS function:
>
> SELECT permit_id
> FROM faps_permit
> WHERE person_id = (select person_id from person where person_id = 100)
> AND cancel_permit is null
> AND location = 'Kent Forest'
> AND OVERLAPS ('27-06-02, '11-07-02', date_from, date_to)
> ORDER BY permit_id ;
>
> This will give you all permits that started, ended or were
> held during the
> named period.
>
> -Josh Berkus
>
>
>
