>
> The problem with your change is that it won't pick up partial overlays.
Just run it. We actually need to test it twice, proving that the
negator is the same as the operator is too much work for now:
BEGIN;
CREATE TABLE yourTable (
startDateField DATE,
endDateField DATE);
INSERT INTO yourTable
VALUES ('2003-11-25', '2003-12-15');
SELECT *
FROM yourtable
WHERE startDateField < '2003-12-31'
AND endDateField > '2003-12-01';
TRUNCATE TABLE yourTable;
INSERT INTO yourTable
VALUES ('2003-12-01', '2003-12-31');
SELECT *
FROM yourtable
WHERE startDateField < '2003-12-15'
AND endDateField > '2003-11-25';
DROP TABLE yourTable;
COMMIT;
> Mine works if you consider a few things.
> 1) the startdate and enddate have to be fully qualified datetimes.
> 2) there was one error an extra set of () needed to be included.
>
> So this works:
>
> select count(idField)
> from yourTable
> where type = 'yourtype'
> and ((startDateField between '12/01/2003 00:00:00' and '12/31/2003
> 23:59:59')
> OR
> (endDateField between '12/01/2003 00:00:00' and '12/31/2003
> 23:59:59'))
Just run it. One test is enough here :-)
BEGIN;
CREATE TABLE yourTable (
startDateField TIMESTAMP,
endDateField TIMESTAMP);
INSERT INTO yourTable
VALUES ('2003-11-25 00:00:00', '2004-12-15 23:59:59');
SELECT *
FROM yourtable
WHERE ((startDateField between '2003-12-01 00:00:00' and
'2003-12-31 23:59:59')
OR
(endDateField between '2003-12-01 00:00:00' and
'2003-12-31 23:59:59'));
SELECT *
FROM yourtable
ROLLBACK;
> The modified version that you had below had an and in it between the 2 date
> tests which would mean that it wouldn't pick up overlays like ("11-25-2003",
> "12-15-2003") & ("12-01-2003", "12-31-2003")
Just test it :-)
> If this doesn't work for you, let me know because it works for me.
It doesn't work for me. Please run the above queryies through a
command line tool and show us the output.
Jochem
--
Who needs virtual reality
if you can just dream?
- Loesje
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
