query between 2 dates error

Having this table with sales
select * from viewcomis2 where date between '05/27/2005' and '05/30/2005'
I got this weird result:
05/27/2005
04/28/2005 <-- this is not suppose to be here
05/28/2005
05/30/2005
I solved:
((substring(datee,1,2)) >= (substring('" & DTPicker1.Value & "',1,2)) and
(substring(datee,4,2)) >= (substring('" & DTPicker1.Value & "',4,2)) and
(substring(datee,7,4)) >= (substring('" & DTPicker1.Value & "',7,4))) AND
((substring(datee,1,2)) <= (substring('" & DTPicker2.Value & "',1,2)) and
(substring(datee,4,2)) <= (substring('" & DTPicker2.Value & "',4,2)) and
(substring(datee,7,4)) <= (substring('" & DTPicker2.Value & "',7,4)))
but still: 05-27-2005 >= 04-27-2005 ?
it was the only way to solve my problem but still have not answered my
original question on why some dates from others months appeared between
5.1.2005 and 5.30.2005
ah-> datee was from a view where convert(char(10),dbo.venta.SaleDate,103)
as datee
SaleDate is Datetime type, but I need to group them by day so I converted
to char(10) in order to query between dates. I know I did something wrong
somewhere but I have no clue on what or where. Any Ideas? ThanksHi
Did you try it this way:
select * from viewcomis2 where cast(date as datetime) between '05/27/2005'
and '05/30/2005'
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"humberto gonzalez" wrote:

> Having this table with sales
> select * from viewcomis2 where date between '05/27/2005' and '05/30/2005'
> I got this weird result:
> 05/27/2005
> 04/28/2005 <-- this is not suppose to be here
> 05/28/2005
> 05/30/2005
> I solved:
> ((substring(datee,1,2)) >= (substring('" & DTPicker1.Value & "',1,2)) and
> (substring(datee,4,2)) >= (substring('" & DTPicker1.Value & "',4,2)) and
> (substring(datee,7,4)) >= (substring('" & DTPicker1.Value & "',7,4))) AND
> ((substring(datee,1,2)) <= (substring('" & DTPicker2.Value & "',1,2)) and
> (substring(datee,4,2)) <= (substring('" & DTPicker2.Value & "',4,2)) and
> (substring(datee,7,4)) <= (substring('" & DTPicker2.Value & "',7,4)))
> but still: 05-27-2005 >= 04-27-2005 ?
> it was the only way to solve my problem but still have not answered my
> original question on why some dates from others months appeared between
> 5.1.2005 and 5.30.2005
> ah-> datee was from a view where convert(char(10),dbo.venta.SaleDate,103)
> as datee
> SaleDate is Datetime type, but I need to group them by day so I converted
> to char(10) in order to query between dates. I know I did something wrong
> somewhere but I have no clue on what or where. Any Ideas? Thanks
>