Re: 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






---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)