Running your code on MSSql2K returns:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ';'.
Removing the begin; then returns:
Server: Msg 2715, Level 16, State 7, Line 1
Column or parameter #1: Cannot find data type DATE.
MSSql2K doesn't have the date type, only smalldatetime and datetime both of
which include a time in the data stored.
I modified the code to run:
BEGIN
declare @yourTable table(
EntryNo int,
startDateField datetime,
endDateField datetime
)
insert into @yourTable(entryNo, startDateField, endDateField)
values(1, '11-25-2003', '12-15-2003')
insert into @yourTable(entryNo, startDateField, endDateField)
values(2, '12-31-2003', '01-15-2004')
insert into @yourTable(entryNo, startDateField, endDateField)
values(3, '11-25-2003', '12-01-2003')
insert into @yourTable(entryNo, startDateField, endDateField)
values(4, '1-25-2004', '2-15-2004')
insert into @yourTable(entryNo, startDateField, endDateField)
values(5, '12-25-2003', '12-30-2003')
select *
from @yourTable
where startDateField < '12-31-2003'
and endDateField > '12-01-2003'
delete from @yourTable
end
This worked but missed entries 2 and 3, mine picked them up immediately.
Granted just adding = to the > and < in yours would fix this.
I will say this though, for Candace's problem your solution with the added =
is probably better. When I wrote what I sent out earlier, a co-worker had
called me with a similar problem BUT he needed to see overlaps where someone
screwed up and had the endDateField before the startDateField. (He was
working on a database that someone else had screwed up!) I tried solving
both problems at the same time instead of trying to find the best solution
for each.
So, I stand humbled at your streamlining expertise.
Steve
-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 4:22 PM
To: CF-Talk
Subject: Re: Date Range Query Issues
DURETTE, STEVEN J (AIT) wrote:
>
> 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]
