|
 |
asp_databases thread: database query login help needed
Message #1 by ckoski@w... on Thu, 17 Aug 2000 11:58:23 -0400
|
|
Hi,
I'm trying to write a SQL Server 6.5 statement for my ASP page, but the
logic is killing me... I need smart helpers.. :)
I have a table with records that have a start and an end date... I have an
ASP page that passes two dates (date1 and date2) to the SQL statement to
search for records that match these criteria:
date1 cannot be less than start date AND date2 cannot be less that start
date
OR
date1 cannot be greater than end date AND date2 cannot be greater than end
date
how should I approach building this as part of the WHERE clause in my SQL
statement? I am too boggled for words... my boolean logic isn't as it used
to be...
TIA,
Cory
Message #2 by mkrishna - Internet Mail <mkrishna@c...> on Thu, 17 Aug 2000 12:17:55 -0400
|
|
select col1, col2,....
from <your_table>
Where (start_date< date1 and
start_date< date2) or
(end_date > date1 and
end_date > date2)
guess this helps!!!
> -----Original Message-----
> From: ckoski
> Sent: Thursday, August 17, 2000 11:58 AM
> To: ASP Databases
> Subject: [asp_databases] database query login help needed
>
> Hi,
>
> I'm trying to write a SQL Server 6.5 statement for my ASP page, but the
> logic is killing me... I need smart helpers.. :)
>
> I have a table with records that have a start and an end date... I have
> an
> ASP page that passes two dates (date1 and date2) to the SQL statement to
> search for records that match these criteria:
>
> date1 cannot be less than start date AND date2 cannot be less that start
> date
>
> OR
>
> date1 cannot be greater than end date AND date2 cannot be greater than end
> date
>
>
> how should I approach building this as part of the WHERE clause in my SQL
> statement? I am too boggled for words... my boolean logic isn't as it
> used
> to be...
>
> TIA,
>
> Cory
>
>
>
>
Message #3 by "Ken Schaefer" <ken@a...> on Fri, 18 Aug 2000 13:13:36 +1000
|
|
Hi Cory,
I've done booking applications that have had the same convoluted logic, and
it's a real pain to get your head around the permutations sometimes :-)
I'm not sure why you want the particular combination below, but assuming
that you know what you are doing ...
WHERE (Date1 >= StartDate AND Date2 >=StartDate)
OR (Date1 <= EndDate AND Date2 <=EndDate)
HTH
Cheers
Ken
----- Original Message -----
From: "Cory Koski"
To: "ASP Databases" <asp_databases@p...>
Sent: Friday, August 18, 2000 1:58 AM
Subject: [asp_databases] database query login help needed
> Hi,
>
> I'm trying to write a SQL Server 6.5 statement for my ASP page, but the
> logic is killing me... I need smart helpers.. :)
>
> I have a table with records that have a start and an end date... I have
an
> ASP page that passes two dates (date1 and date2) to the SQL statement to
> search for records that match these criteria:
>
> date1 cannot be less than start date AND date2 cannot be less that start
> date
>
> OR
>
> date1 cannot be greater than end date AND date2 cannot be greater than end
> date
>
>
> how should I approach building this as part of the WHERE clause in my SQL
> statement? I am too boggled for words... my boolean logic isn't as it
used
> to be...
>
> TIA,
>
> Cory
Message #4 by "Dallas Martin" <dmartin@z...> on Fri, 18 Aug 2000 06:17:39 -0400
|
|
Wouldnt' the "BETWEEN" operator work here?
WHERE ( (startdate BETWEEN date1 and date2) OR
(enddate BETWEEN date1 and date2) )
Dallas
----- Original Message -----
From: "Ken Schaefer"
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, August 17, 2000 11:13 PM
Subject: [asp_databases] Re: database query login help needed
> Hi Cory,
>
> I've done booking applications that have had the same convoluted logic,
and
> it's a real pain to get your head around the permutations sometimes :-)
>
> I'm not sure why you want the particular combination below, but assuming
> that you know what you are doing ...
>
> WHERE (Date1 >= StartDate AND Date2 >=StartDate)
> OR (Date1 <= EndDate AND Date2 <=EndDate)
>
> HTH
>
> Cheers
> Ken
>
> ----- Original Message -----
> From: "Cory Koski"
> To: "ASP Databases" <asp_databases@p...>
> Sent: Friday, August 18, 2000 1:58 AM
> Subject: [asp_databases] database query login help needed
>
>
> > Hi,
> >
> > I'm trying to write a SQL Server 6.5 statement for my ASP page, but the
> > logic is killing me... I need smart helpers.. :)
> >
> > I have a table with records that have a start and an end date... I have
> an
> > ASP page that passes two dates (date1 and date2) to the SQL statement to
> > search for records that match these criteria:
> >
> > date1 cannot be less than start date AND date2 cannot be less that start
> > date
> >
> > OR
> >
> > date1 cannot be greater than end date AND date2 cannot be greater than
end
> > date
> >
> >
> > how should I approach building this as part of the WHERE clause in my
SQL
> > statement? I am too boggled for words... my boolean logic isn't as it
> used
> > to be...
> >
> > TIA,
> >
> > Cory
>
Message #5 by ckoski@w... on Fri, 18 Aug 2000 11:22:59 -0400
|
|
Ken... I think I figured it out...
I made a few asumptions with the logic, and I was able to ultra-simplify the
WHERE clause...
WHERE id = @id
AND not (start_date > @enddate or end_date < @startdate)
(I'm using a stored proc)... I assumed that selected @startdate shouldn't
be larger than the actual end_date in the DB and the @enddate shouldn't be
larger than the actual start_date ... this is by virtue of the fact that my
@startdate <= @enddate always...
I just test for those two conditions... either test can set the (---)
portion to true and I NOT the result and voila... it works :) I get all
the records I want...
now if I could just figure out how to make my dates work properly... when I
enter in 2/3/00 the dumb server doesn't know it's month/day/year...
grrr.... it confuses it... it sometimes returns back my date 3/2/2000
argh!
----- Original Message -----
From: "Ken Schaefer"
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, August 17, 2000 11:13 PM
Subject: [asp_databases] Re: database query login help needed
> Hi Cory,
>
> I've done booking applications that have had the same convoluted logic,
and
> it's a real pain to get your head around the permutations sometimes :-)
>
> I'm not sure why you want the particular combination below, but assuming
> that you know what you are doing ...
>
> WHERE (Date1 >= StartDate AND Date2 >=StartDate)
> OR (Date1 <= EndDate AND Date2 <=EndDate)
>
> HTH
>
> Cheers
> Ken
>
> ----- Original Message -----
> From: "Cory Koski"
> To: "ASP Databases" <asp_databases@p...>
> Sent: Friday, August 18, 2000 1:58 AM
> Subject: [asp_databases] database query login help needed
>
>
> > Hi,
> >
> > I'm trying to write a SQL Server 6.5 statement for my ASP page, but the
> > logic is killing me... I need smart helpers.. :)
> >
> > I have a table with records that have a start and an end date... I have
> an
> > ASP page that passes two dates (date1 and date2) to the SQL statement to
> > search for records that match these criteria:
> >
> > date1 cannot be less than start date AND date2 cannot be less that start
> > date
> >
> > OR
> >
> > date1 cannot be greater than end date AND date2 cannot be greater than
end
> > date
> >
> >
> > how should I approach building this as part of the WHERE clause in my
SQL
> > statement? I am too boggled for words... my boolean logic isn't as it
> used
> > to be...
> >
> > TIA,
> >
> > Cory
>
>
> ---
> You are currently subscribed to asp_databases
$subst('Email.Unsub')
>
>
Message #6 by "Ken Schaefer" <ken@a...> on Mon, 21 Aug 2000 11:20:02 +1000
|
|
> now if I could just figure out how to make my dates work properly... when
I
> enter in 2/3/00 the dumb server doesn't know it's month/day/year...
> grrr.... it confuses it... it sometimes returns back my date 3/2/2000
> argh!
Use the format: yyyy/mm/dd
It's easy to write a function to generate yyyy/mm/dd from a given dd/mm/yyyy
(or mm/dd/yyyy) and there's no ambiguity. As someone once told me: not even
the Americans are perverse enough to come up with a yyyy/dd/mm format :-)
Cheers
Ken
|
|
|
View Cart
