RE: Date Range Query Issues
The situation is a little eccentric, as these are not work shifts, they are on-call shifts. So, a person could have a shift that lasted a whole month.
Another thing to note is that while Dr. X's shift ENDS at 8AM 12/5/03, DR. X's BEGINS at 8AM 12/5/03.
:)
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 12/3/2003 12:51:27 PM >>>
To answer your first question, ok, my brain isn't working properly yet. I
was including them because I was just automatically thinking that the shift
would end at the end of the day not the beginning and therefore if someone
chose that day to start there would be an overlap. Using times in the test
would have worked with the > and <. I stand corrected again.
As for the second question. I really need to get over the flu before I hit
reply. You are right. I completely forgot to deal with date ranges that
are larger than my select range.
Now I am going to go take some Dayquil and admit defeat.
Steve
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 03, 2003 10:08 AM
To: CF-Talk
Subject: Re: Date Range Query Issues
DURETTE, STEVEN J (AIT) wrote:
>
> 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.
It is supposed to miss them. If a persons shift ends at X, why
shouldn't the next shift of that person be allowed to start at X
either?
> I will say this though, for Candace's problem your solution with the added
=
> is probably better.
Even without the added =. Your solution still does not find any
that completely envelope other ranges. PostgreSQL shows:
jochemd=> BEGIN;
jochemd=> CREATE TABLE yourTable (
jochemd(> startDateField TIMESTAMP,
jochemd(> endDateField TIMESTAMP);
jochemd=> INSERT INTO yourTable
jochemd-> VALUES ('2003-11-25 00:00:00', '2004-12-15 23:59:59');
jochemd=>
jochemd=>
jochemd=> SELECT *
jochemd-> FROM yourtable
jochemd-> WHERE ((startDateField between '2003-12-01 00:00:00'
and '2003-12-31 23:59:59')
jochemd(> OR
jochemd(> (endDateField between '2003-12-01 00:00:00'
and '2003-12-31 23:59:59'));
startdatefield | enddatefield
+--
(0 rows)
jochemd=>
jochemd=> ROLLBACK;
Jochem
--
Who needs virtual reality
if you can just dream?
- Loesje
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Date Range Query Issues
To answer your first question, ok, my brain isn't working properly yet. I
was including them because I was just automatically thinking that the shift
would end at the end of the day not the beginning and therefore if someone
chose that day to start there would be an overlap. Using times in the test
would have worked with the > and <. I stand corrected again.
As for the second question. I really need to get over the flu before I hit
reply. You are right. I completely forgot to deal with date ranges that
are larger than my select range.
Now I am going to go take some Dayquil and admit defeat.
Steve
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 03, 2003 10:08 AM
To: CF-Talk
Subject: Re: Date Range Query Issues
DURETTE, STEVEN J (AIT) wrote:
>
> 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.
It is supposed to miss them. If a persons shift ends at X, why
shouldn't the next shift of that person be allowed to start at X
either?
> I will say this though, for Candace's problem your solution with the added
=
> is probably better.
Even without the added =. Your solution still does not find any
that completely envelope other ranges. PostgreSQL shows:
jochemd=> BEGIN;
jochemd=> CREATE TABLE yourTable (
jochemd(> startDateField TIMESTAMP,
jochemd(> endDateField TIMESTAMP);
jochemd=> INSERT INTO yourTable
jochemd-> VALUES ('2003-11-25 00:00:00', '2004-12-15 23:59:59');
jochemd=>
jochemd=>
jochemd=> SELECT *
jochemd-> FROM yourtable
jochemd-> WHERE ((startDateField between '2003-12-01 00:00:00'
and '2003-12-31 23:59:59')
jochemd(> OR
jochemd(> (endDateField between '2003-12-01 00:00:00'
and '2003-12-31 23:59:59'));
startdatefield | enddatefield
+--
(0 rows)
jochemd=>
jochemd=> ROLLBACK;
Jochem
--
Who needs virtual reality
if you can just dream?
- Loesje
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Date Range Query Issues
DURETTE, STEVEN J (AIT) wrote:
>
> 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.
It is supposed to miss them. If a persons shift ends at X, why
shouldn't the next shift of that person be allowed to start at X
either?
> I will say this though, for Candace's problem your solution with the added =
> is probably better.
Even without the added =. Your solution still does not find any
that completely envelope other ranges. PostgreSQL shows:
jochemd=> BEGIN;
jochemd=> CREATE TABLE yourTable (
jochemd(> startDateField TIMESTAMP,
jochemd(> endDateField TIMESTAMP);
jochemd=> INSERT INTO yourTable
jochemd-> VALUES ('2003-11-25 00:00:00', '2004-12-15 23:59:59');
jochemd=>
jochemd=>
jochemd=> SELECT *
jochemd-> FROM yourtable
jochemd-> WHERE ((startDateField between '2003-12-01 00:00:00'
and '2003-12-31 23:59:59')
jochemd(> OR
jochemd(> (endDateField between '2003-12-01 00:00:00'
and '2003-12-31 23:59:59'));
startdatefield | enddatefield
+--
(0 rows)
jochemd=>
jochemd=> ROLLBACK;
Jochem
--
Who needs virtual reality
if you can just dream?
- Loesje
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Date Range Query Issues
Jochem,
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]
Re: Date Range Query Issues
Jochem van Dieten wrote: > 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: On second thought, the proof that I am not selecting too few records is pretty easy. Let A be an interval from Ai to Aj: Ai < Aj. Let B be an interval from Bi to Bj: Bi < Bj. For A and B to overlap, there must be a point in time X that is in both the interval A and the interval B. Ergo: Ai < X < Aj ^ Bi < X < Bj From which follows: Ai < X < Bj ^ Bi < X < Aj We can leave out X: Ai < Bj ^ Bi < Aj And this can be written as: Ai < Bj ^ Aj > Bi This is the exact condition I was testing for in the query. I might be getting too many results back, but not too few. Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Date Range Query Issues
Tony Weeg wrote: > sure...where? http://register.microsoft.com/mswish/suggestion.asp?from=cu&fu=%2Fisapi%2Fgomscom%2Easp%3Ftarget%3D%2Fmswish%2Fthanks%2Ehtm > www.microsoft.com/likeTheyCare.cfm You mean you buy software from a company that doesn't care about its customers? Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Date Range Query Issues
sure...where? www.microsoft.com/likeTheyCare.cfm ...tony tony weeg senior web applications architect navtrak, inc. www.navtrak.net [EMAIL PROTECTED] 410.548.2337 -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 4:50 PM To: CF-Talk Subject: Re: Date Range Query Issues Tony Weeg wrote: > not going to work in sql server 2000, not from what I can see in the > docs File an enhancement request :-) Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Date Range Query Issues
Tony Weeg wrote: > not going to work in sql server 2000, not from what I can see in the > docs File an enhancement request :-) Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
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]
RE: Date Range Query Issues
Jochem,
OVERLAPS isn't a valid MSSql2K TSQL command. I looked it up in the MSSQL
Books online.
Steve
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 3:38 PM
To: CF-Talk
Subject: Re: Date Range Query Issues
Mickael wrote:
>
> Could you show me a statement where that is used, it sounds interesting
just don't know how it is used.
Instead of
SELECT x
FROM yourtable
WHERE startDateField < '#formEndDate#'
AND endDateField > #formStartDate#'
one would use:
SELECT x
FROM type = 'checktype'
AND (startDateField, endDateField) OVERLAPS
('#formEndDate#', #formStartDate#')
Jochem
--
Who needs virtual reality
if you can just dream?
- Loesje
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Date Range Query Issues
Jochem,
The problem with your change is that it won't pick up partial overlays.
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'))
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")
If this doesn't work for you, let me know because it works for me.
Steve
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 3:31 PM
To: CF-Talk
Subject: Re: Date Range Query Issues
DURETTE, STEVEN J (AIT) wrote:
>
> But you can use between
>
> select count(idField)
> from yourtable
> where type = 'checktype'
> and (startDateField between 'startdate' and 'enddate'
> OR endDateField between 'startdate and 'enddate')
That won't work, it won't detect the following overlap:
('2003-12-01', '2003-12-31') & ('2003-12-02', '2003-12-30')
You need a statement that is actually even simpler:
SELECT COUNT(idField)
FROM yourtable
WHERE type = 'checktype'
AND startDateField <
value="#formEndDate#">
AND endDateField >
value="#formStartDate#">
Just make sure you verify that the user submitted startdate is
less than the user submitted enddate.
Jochem
--
Who needs virtual reality
if you can just dream?
- Loesje
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Date Range Query Issues
Mickael wrote:
>
> Could you show me a statement where that is used, it sounds interesting just don't know how it is used.
Instead of
SELECT x
FROM yourtable
WHERE startDateField < '#formEndDate#'
AND endDateField > #formStartDate#'
one would use:
SELECT x
FROM type = 'checktype'
AND (startDateField, endDateField) OVERLAPS
('#formEndDate#', #formStartDate#')
Jochem
--
Who needs virtual reality
if you can just dream?
- Loesje
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Date Range Query Issues
DURETTE, STEVEN J (AIT) wrote:
>
> But you can use between
>
> select count(idField)
> from yourtable
> where type = 'checktype'
> and (startDateField between 'startdate' and 'enddate'
> OR endDateField between 'startdate and 'enddate')
That won't work, it won't detect the following overlap:
('2003-12-01', '2003-12-31') & ('2003-12-02', '2003-12-30')
You need a statement that is actually even simpler:
SELECT COUNT(idField)
FROM yourtable
WHERE type = 'checktype'
AND startDateField <
value="#formEndDate#">
AND endDateField >
value="#formStartDate#">
Just make sure you verify that the user submitted startdate is
less than the user submitted enddate.
Jochem
--
Who needs virtual reality
if you can just dream?
- Loesje
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Date Range Query Issues
Thanks guys ;) Now I just have to figure out how screwed (or unscrewed) I am for breaking up the dates.
I've got a bit of work ahead of me and I'll let you know how it turns out.
Thanks again!
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 12/2/2003 12:49:01 PM >>>
No, I just tried it and I couldn't get it to work.
But you can use between
select count(idField)
from yourtable
where type = 'checktype'
and (startDateField between 'startdate' and 'enddate'
OR endDateField between 'startdate and 'enddate')
Of course change all of the 'variables' to either cfqueryparams in cf or
@variables in TSQL.
Steve
-Original Message-
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 12:05 PM
To: CF-Talk
Subject: RE: Date Range Query Issues
not going to work in sql server 2000, not from what I can see in the
docs
..tony
tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548.2337
-Original Message-
From: Candace Cottrell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 10:22 AM
To: CF-Talk
Subject: Re: Date Range Query Issues
Oh wow... I didnt know about that... I'm using MSSQL 2000...
I'll try it out when I get back from my meeting. :)
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 12/2/2003 10:12:16 AM >>>
Candace Cottrell said:
>
> 1 - Do you mean natively or the way I have the db set up?
Natively, OVERLAPS is a SQL predicate:
('2003-12-01', '2003-12-31') OVERLAPS ('2003-12-16', '2004-01-15')
Jochem
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Date Range Query Issues
Hi Jochem,
Could you show me a statement where that is used, it sounds interesting just don't know how it is used.
Thanks
Mike
- Original Message -
From: Jochem van Dieten
To: CF-Talk
Sent: Tuesday, December 02, 2003 10:12 AM
Subject: Re: Date Range Query Issues
Candace Cottrell said:
>
> 1 - Do you mean natively or the way I have the db set up?
Natively, OVERLAPS is a SQL predicate:
('2003-12-01', '2003-12-31') OVERLAPS ('2003-12-16', '2004-01-15')
Jochem
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Date Range Query Issues
No, I just tried it and I couldn't get it to work.
But you can use between
select count(idField)
from yourtable
where type = 'checktype'
and (startDateField between 'startdate' and 'enddate'
OR endDateField between 'startdate and 'enddate')
Of course change all of the 'variables' to either cfqueryparams in cf or
@variables in TSQL.
Steve
-Original Message-
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 12:05 PM
To: CF-Talk
Subject: RE: Date Range Query Issues
not going to work in sql server 2000, not from what I can see in the
docs
...tony
tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548.2337
-Original Message-
From: Candace Cottrell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 10:22 AM
To: CF-Talk
Subject: Re: Date Range Query Issues
Oh wow... I didnt know about that... I'm using MSSQL 2000...
I'll try it out when I get back from my meeting. :)
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 12/2/2003 10:12:16 AM >>>
Candace Cottrell said:
>
> 1 - Do you mean natively or the way I have the db set up?
Natively, OVERLAPS is a SQL predicate:
('2003-12-01', '2003-12-31') OVERLAPS ('2003-12-16', '2004-01-15')
Jochem
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Date Range Query Issues
not going to work in sql server 2000, not from what I can see in the
docs
...tony
tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548.2337
-Original Message-
From: Candace Cottrell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 10:22 AM
To: CF-Talk
Subject: Re: Date Range Query Issues
Oh wow... I didnt know about that... I'm using MSSQL 2000...
I'll try it out when I get back from my meeting. :)
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 12/2/2003 10:12:16 AM >>>
Candace Cottrell said:
>
> 1 - Do you mean natively or the way I have the db set up?
Natively, OVERLAPS is a SQL predicate:
('2003-12-01', '2003-12-31') OVERLAPS ('2003-12-16', '2004-01-15')
Jochem
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Date Range Query Issues
Oh wow... I didnt know about that... I'm using MSSQL 2000...
I'll try it out when I get back from my meeting. :)
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 12/2/2003 10:12:16 AM >>>
Candace Cottrell said:
>
> 1 - Do you mean natively or the way I have the db set up?
Natively, OVERLAPS is a SQL predicate:
('2003-12-01', '2003-12-31') OVERLAPS ('2003-12-16', '2004-01-15')
Jochem
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Date Range Query Issues
Candace, If I were you, I would store your dates and times in the db as a datetime field. Then it will become much easier to do the where statement. If you just want to find out if there is an overlap then you could use. select count(identityField) as overlap from yourTable where shiftType = value="#formType#"> and ((startDate >= value="#formStartDate#"> and startDate <= value="#formEndDate#">) or (endDate >= value="#formStartDate#"> and endDate <= value="#formEndDate#">) Of course you will have to make sure that your form dates and times conform to your database. You wouldn't want the user to select 5 (meaning 5pm) and have come in as 5 am. Let me know if you need more clarification. Steve -Original Message- From: Candace Cottrell [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 10:03 AM To: CF-Talk Subject: Re: Date Range Query Issues Hey Jochem :) 1 - Do you mean natively or the way I have the db set up? If it's the latter, I don't have any constraints for overlaps in the db design. Each "shift" or date range is its own record. The id is an identity field. Candace K. Cottrell, Web Developer The Children's Medical Center One Children's Plaza Dayton, OH 45404 937-641-4293 http://www.childrensdayton.org [EMAIL PROTECTED] >>> [EMAIL PROTECTED] 12/2/2003 9:57:43 AM >>> > > The problems are: > > 1) I can't figure out what should be in the WHERE clause. Does your database support OVERLAPS? > 2) Have I screwed myself by chopping up the dates? Screwed is not a nice word, but you did make things difficult for yourself. Jochem _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Date Range Query Issues
Candace Cottrell said:
>
> 1 - Do you mean natively or the way I have the db set up?
Natively, OVERLAPS is a SQL predicate:
('2003-12-01', '2003-12-31') OVERLAPS ('2003-12-16', '2004-01-15')
Jochem
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Date Range Query Issues
Hey Jochem :) 1 - Do you mean natively or the way I have the db set up? If it's the latter, I don't have any constraints for overlaps in the db design. Each "shift" or date range is its own record. The id is an identity field. Candace K. Cottrell, Web Developer The Children's Medical Center One Children's Plaza Dayton, OH 45404 937-641-4293 http://www.childrensdayton.org [EMAIL PROTECTED] >>> [EMAIL PROTECTED] 12/2/2003 9:57:43 AM >>> > > The problems are: > > 1) I can't figure out what should be in the WHERE clause. Does your database support OVERLAPS? > 2) Have I screwed myself by chopping up the dates? Screwed is not a nice word, but you did make things difficult for yourself. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Date Range Query Issues
Candace Cottrell said: > > Start_Month 12 > Start_Day 1 > Start_Year 2003 > Start_Time 8 > > End_Month 12 > End_Day 15 > End_Year 2003 > End_Time 8 > I know I need to pull a query that brings back the records that > would be overlapped. And if that recordset is empty, proceed with > the insert. Otherwise, give the user a message that there is an > overlap and take them back to the form. > > The problems are: > > 1) I can't figure out what should be in the WHERE clause. Does your database support OVERLAPS? > 2) Have I screwed myself by chopping up the dates? Screwed is not a nice word, but you did make things difficult for yourself. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
