Home > Archive > SQL Server Programming > May 2005 > query between 2 dates error
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
query between 2 dates error
|
|
| humberto gonzalez 2005-05-31, 4:02 am |
| 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
| |
| Chandra 2005-05-31, 4:02 am |
| Hi
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
>
|
|
|
|
|