Between operator does not bring the 2 extremes in the result set?

Hi,
Between operator does not bring the 2 extremes in the result set?
As in:
Select * from HT_DBAU001_Agenda where AgDtHora between '1/1/2003' and
'31/12/2003' order by AgDtHora
At this case here does not bring me the 2 extremes in the result set
'1/1/2003' and '31/12/2003', only
brings me what is between '1/1/2003' and '31/12/2003'.
Is it there any solution in Between operator without use >= and <= '?
Thanks,
VILMAR
BRAZIL
PRAIA GRANDE/SPThis is a good example of why it is important to include ddl as well as
sample data. Assuming that AgDtHora columns is either datetime or small
datetime. The use of "between" will include the appropriate data. However,
you have failed to properly consider the time portion of the values stored
in the column as well as the "arguments" used for "between". Using
"between" will return all rows that have a value that falls at any time on
the lower date boundary. It will only include rows with values that fall at
precisely the beginning of time on the upper boundary (i.e., Dec 31 2003
00:00:00.000) since you only specified the date portion of the appropriate
datatype. So if you really want to use "between", you need to fully qualify
the upper argument as '20031231 23:59:59.997' (for datetime) or '20031231
23:59:59' (for smalldatetime).
create table #test (id int not null, tdate datetime not null)
insert #test (id, tdate) values (2, '20031225 01:00:00.003')
insert #test (id, tdate) values (1, '20031225')
insert #test (id, tdate) values (3, '20031226 12:24:01.997')
insert #test (id, tdate) values (4, '20031226')
select * from #test
select * from #test where tdate between '20031225' and '20031226'
"Vilmar Brazão de Oliveira" <suporte@.hitecnet.com.br> wrote in message
news:%23xegAj%23yDHA.1724@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Between operator does not bring the 2 extremes in the result set?
> As in:
> Select * from HT_DBAU001_Agenda where AgDtHora between '1/1/2003' and
> '31/12/2003' order by AgDtHora
> At this case here does not bring me the 2 extremes in the result set
> '1/1/2003' and '31/12/2003', only
> brings me what is between '1/1/2003' and '31/12/2003'.
> Is it there any solution in Between operator without use >= and <= '?
> Thanks,
> VILMAR
> BRAZIL
> PRAIA GRANDE/SP
>|||OK,
Thanks,
VILMAR
BRAZIL
PRAIA GRANDE/SP
"Scott Morris" <bogus@.bogus.com> escreveu na mensagem
news:#L7lv7#yDHA.560@.TK2MSFTNGP11.phx.gbl...
> This is a good example of why it is important to include ddl as well as
> sample data. Assuming that AgDtHora columns is either datetime or small
> datetime. The use of "between" will include the appropriate data.
However,
> you have failed to properly consider the time portion of the values stored
> in the column as well as the "arguments" used for "between". Using
> "between" will return all rows that have a value that falls at any time on
> the lower date boundary. It will only include rows with values that fall
at
> precisely the beginning of time on the upper boundary (i.e., Dec 31 2003
> 00:00:00.000) since you only specified the date portion of the appropriate
> datatype. So if you really want to use "between", you need to fully
qualify
> the upper argument as '20031231 23:59:59.997' (for datetime) or '20031231
> 23:59:59' (for smalldatetime).
>
> create table #test (id int not null, tdate datetime not null)
> insert #test (id, tdate) values (2, '20031225 01:00:00.003')
> insert #test (id, tdate) values (1, '20031225')
> insert #test (id, tdate) values (3, '20031226 12:24:01.997')
> insert #test (id, tdate) values (4, '20031226')
> select * from #test
> select * from #test where tdate between '20031225' and '20031226'
>
> "Vilmar Brazão de Oliveira" <suporte@.hitecnet.com.br> wrote in message
> news:%23xegAj%23yDHA.1724@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> > Between operator does not bring the 2 extremes in the result set?
> > As in:
> > Select * from HT_DBAU001_Agenda where AgDtHora between '1/1/2003' and
> > '31/12/2003' order by AgDtHora
> > At this case here does not bring me the 2 extremes in the result set
> > '1/1/2003' and '31/12/2003', only
> > brings me what is between '1/1/2003' and '31/12/2003'.
> > Is it there any solution in Between operator without use >= and <= '?
> > Thanks,
> >
> > VILMAR
> > BRAZIL
> > PRAIA GRANDE/SP
> >
> >
>

between operator behavior

In sql server,teradata,oracle when you use between operator to query the
table it shows as below
Where 1 between 1 and 10 -> True
Where 1 between 10 and 1 -> False
Logically speaking the above method is wrong so I want to know why the
implementation is done like this across all databases.
Appreciate your help in understanding the myth.
Thanks
BETWEEN internally operates by expanding the expression to:
WHERE 1 >= (lower bound) AND 1 <= (upper bound)
That does not work for WHERE 1 BETWEEN 10 AND 1 ...
WHERE 1 >= 10 --FALSE
"PraveenMohan" <PraveenMohan@.discussions.microsoft.com> wrote in message
news:41C2CFE5-3A25-4678-A395-3EAAD516EAC9@.microsoft.com...
> In sql server,teradata,oracle when you use between operator to query the
> table it shows as below
> Where 1 between 1 and 10 -> True
> Where 1 between 10 and 1 -> False
> Logically speaking the above method is wrong so I want to know why the
> implementation is done like this across all databases.
> Appreciate your help in understanding the myth.
> Thanks
>
|||Because that's how it's defined in the ANSI/ISO SQL Standard.
X BETWEEN Y AND Z
is equivalent to
X>=Y AND X<=Z
therefore:
1 BETWEEN 10 AND 1
= 1>=10 AND 1<=1
= FALSE
It might seem "illogical" if you have some different intuitive model of what
"between" means but to me the only obvious alternative is to raise an error
if Y>Z. I think that would be a lot less convenient since you would
constantly have to implement bounds checking before using BETWEEN.
David Portas
SQL Server MVP
|||Just some useless info to add...:
Some earlier version of SQL Server did actually return rows even if the values are "swapped". My
guess is that 6.0 was the version that changed this as MS did a lot of ANSI SQL work on that
version. Imagine the number of bugs creeped up in sw, with 6.0... :-).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ACDBB972-14E2-4240-8E8A-D4EABC5D13FA@.microsoft.com...
> Because that's how it's defined in the ANSI/ISO SQL Standard.
> X BETWEEN Y AND Z
> is equivalent to
> X>=Y AND X<=Z
> therefore:
> 1 BETWEEN 10 AND 1
> = 1>=10 AND 1<=1
> = FALSE
> It might seem "illogical" if you have some different intuitive model of what
> "between" means but to me the only obvious alternative is to raise an error
> if Y>Z. I think that would be a lot less convenient since you would
> constantly have to implement bounds checking before using BETWEEN.
> --
> David Portas
> SQL Server MVP
> --
>

between operator

Hi,

I'm having problems using the between operator in a query statement.

Select *
from <mytable>
where date between @.date1 and @.date2

The date values with a hour specified, aren't returned. What is the approach you would recommend here?

Thx

EDIT: by playing with this problem I've figured out I can append the hour to the date like this: <date> between @.fromDate + '00:00:00' and @.toDate + '23:59:59'

this seem to work, but I'm not sure if this is correct

Select *
from <mytable>
where date >= @.date1 and date < dateadd(d,1,@.date2)

or

Select *
from <mytable>
where date >= @.date1 and date < @.date2 +1

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

i had a similar problem which i did through the application side:

string weekdate =txtFrom.Text;

string daydate = System.Convert.ToDateTime(txtTo.Text).Add(System.TimeSpan.FromDays(1)).ToString();

and later...

TechnicianViewInboxDataAdapter.SelectCommand.CommandText = tempselecttechnician + " where status = 1 and TechnicianID = " + Session["UserID"].ToString() + "and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' or TechnicianID = 1 and DepartmentID = 1 and status = 1 and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' or TechnicianID = 1 and status = 1 and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' and DepartmentID = " + Session["DepartmentID"].ToString();

this works as well.

the code ends up like this:

SELECT FirstName, LastName, Email, CallNumber, DepartmentName, UserName, Status, TechnicianID, CallLoggedDT, DepartmentID, CallOpenedDT, CallActionedDT, CallClosedDT FROM dbo.TechnicianInboxView where status = 1 and TechnicianID = 2and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' or TechnicianID = 1 and DepartmentID = 1 and status = 1 and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' or TechnicianID = 1 and status = 1 and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' and DepartmentID = 2

|||

You can also use the datediff function and the statement would look like this.

Select * from <mytable> where

datediff(dd,@.date1,<Datefield in the table>)>=0 and datediff(dd,@.date2,<Datefield in the table>)<=0

@.Date can be varchar too and it can be in the format of "mm/dd/yy" also. Need not have the time factor.

|||

where datediff(dd,@.date1,<Datefield in the table>)>=0 and datediff(dd,@.date2,<Datefield in the table>)<=0

Would not recommend doing so in the terms of performace. You will never get index seek over the index for datefield column: it will always index scan at the best.