Query between Times

i have two DateTime fields One called 'Open' and one 'Closed' . I need to
know if a given time is inbetween the Open and Closed times ?Here's an example:
declare @.dt datetime
declare @.OpenDate datetime
declare @.CloseDate datetime
set @.dt = '1/5/2005'
set @.OpenDate = '1/1/2005'
set @.CloseDate = '1/30/2005'
if @.dt >= @.OpenDate and @.dt <= @.CloseDate
print 'yep'
else
print 'nope'
Bryce|||Woops, I just noticed the word "QUERY" in your post. Here's another
example:
declare @.dt datetime
declare @.OpenDate datetime
declare @.CloseDate datetime
set @.dt = '1/5/2005'
set @.OpenDate = '1/1/2005'
set @.CloseDate = '1/30/2005'
select case when @.dt >= @.OpenDate and @.dt <= @.CloseDate then 1 else 0
end
Of course, when selecting from a table, the variables can be replaced
with column names.
Bryce|||Assuming Open and Cl;osed values are in Variables @.Open and @.Closed,
If this is being done in a Select Statement, then put a Predicate expression
"GivenTimeColName Between Open And Closed"
in the Where Clause.
Select <col List>
From <Table(S)>
Where <GivenTimeColName > Between Open And Closed
If it's being done in T-SQL Code, just put the predicate after an "If "
If @.GivenTimeVariable Between @.Open And @.Closed
Begin
<T-SQL Statements to execute if true
End
Else
Begin
<T-SQL Statements t oexecute if false>
End
"Peter Newman" wrote:

> i have two DateTime fields One called 'Open' and one 'Closed' . I need to
> know if a given time is inbetween the Open and Closed times ?
>|||Use the BETWEEN keyword, as in:
select blablabla
from blablabla
where a_given_time between Open and Closed
Does this help?
Raj|||> set @.dt = '1/5/2005'
Ugh. Is that January 5th or May 1st? Who knows? It depends on several
factors, including regional settings on the machine, SET LANGUAGE, SET
DATEFORMAT settings, etc.
If you're going to hard code dates in the query like that, at least use a
non-ambiguous format.
SET @.dt = '20050105'

> set @.CloseDate = '1/30/2005'
> if @.dt >= @.OpenDate and @.dt <= @.CloseDate
Ugh again. You are including rows from midnight on the close date, but
nothing else. So if something happened at 12:01, it's ignored by your where
clause. I'm not sure if the original poster wanted to include that day or
use it as a cutoff. It is better to use:
IF @.dt >= '20050105' AND @.dt < '20050130'
or
IF @.dt >= '20050105' AND @.dt < '20050131'
(Depending on whether the data from throughout the day on 20050130 should be
included or not.)|||> Select <col List>
> From <Table(S)>
> Where <GivenTimeColName > Between Open And Closed
FYI, I find BETWEEN a difficult and ambiguous style to use for DATETIME
queries (though it is great for INTs, I will agree). Except in the rare
case where only whole dates (with a midnight timestamp) are stored, it is
always better to use >= start_of_range and < (end_of_range + 1).
http://www.aspfaq.com/2280|||Thanks, Aaron. Very informative.
Bryce|||Aaron,
Is your main rationale for feeling this way becuase of Betweens
inflexible "Inclusive" behavior, which always also returns records which are
equal to the enddate value?
This IS a problem I have not found a clean answer for...
I still like Between, however, because it "reads" much moore naturally, (I
mean closer to the problem you are trying to solve.. It's not a big
difference, I grant you, but when you have constructions like:
Where My_DateCOlumnValue >= @.BeginDateRangeValue And My_DateCOlumnValue <
@.EndDateRangeValue
You have to read the token for the My_DateCOlumnValue twice, in both places,
to ensure that it is indeed the same token... this issue is more significant
when it's NOT a token or column name, but a longer expression based on one o
r
more columns, (Not desired, but sometimes necessary)
In those cases, the Between construction "reads" easier to me than the >= ..
... And ... < ... since the complex expression would only be there once...
But you still have to solve the Inclusive Issue...
on another note, I believe that the Query Processor converts the between
syntax into >= AND <= to execute it anyway ...
"Aaron [SQL Server MVP]" wrote:

> FYI, I find BETWEEN a difficult and ambiguous style to use for DATETIME
> queries (though it is great for INTs, I will agree). Except in the rare
> case where only whole dates (with a midnight timestamp) are stored, it is
> always better to use >= start_of_range and < (end_of_range + 1).
> http://www.aspfaq.com/2280
>
>|||Aaron,
The only point you made worth speaking to is
<<<
Then you will include rows from 20040105 at exactly midnight, but nothing
later. This trips *many* people up, because they assume any row from
20040105 (say, 20040105 15:43:04.872) should be included.
And there are several solutions ot this, some of which I mentioned already.
You're right, this trips up a lot of folks. But I guess your approach is
everybody else (except for you of course) is too stupid to understand this
and therefore should just NOT use such potentially dangerous tools.
There are unfortunately, quite too many people who think that way in this
world.
I prefer to assume, (until proven otherwise) that anyone is capable of
undersstanding the dangers (and the power) of any tool enough to learn to us
e
it intelligently and wisely, at least if someone is kind enough to teach the
m
how, and offer them the knowledge and the opportunity.
So, t oanswer yr final thought, NO, I will not stop attempting to "drown
out" anyone who attempts to limit or restrict what information orr technique
s
are made available, especially whne done because in someone's opinion, some
technique is "too dangerous" for the average person to use...
"Aaron [SQL Server MVP]" wrote:
> places,
> How complex do you name your columns? This wouldn't be an issue if you us
e
> sensible names and avoid nedlessly long and complicated identifiers. And
> unless you are dealing solely with DATETIME columns throughout your entire
> environment, it's something you have to approach from a broader perspectiv
e
> than simply whether to use BETWEEN or separate clauses.
>
> or
> If you are performing calculations against dates on the left side of the
> equation, it is always possible to do tose same calculations on the right
> side of the equation (not only avoiding your complaint, but also giving th
e
> query a better chance to use an index). For example:
> WHERE DATEADD(MONTH, -2, DateTimeColumn) + 1 < '20050101'
> is the same as
> WHERE DateTimeColumn < DATEADD(MONTH, 2, '20050101') - 1
> Comparing these this way is actually more logical, in my opinion, and stil
l
> allows you to use an index and avoid scanning the left side for expression
s
> involving the column name.
>
> Yes, and that's WHY it's a problem. If you have:
> BETWEEN '20040101' AND '20040105'
> Then you will include rows from 20040105 at exactly midnight, but nothing
> later. This trips *many* people up, because they assume any row from
> 20040105 (say, 20040105 15:43:04.872) should be included. If you have the
> true boundary on the right side, you would have to use
> '2004-01-04T23:59:59.997' and pray it doesn't get implicitly converted to
> SMALLDATETIME (which rounds up, yielding the exact same problem).
> But go ahead, keep using BETWEEN. Doesn't sound like the points I'm raisi
ng
> are very important to you anyway (but please don't try to drown them out f
or
> those people who might consider them worthwhile).
> A
>
>