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
> >
> >
>