I want to fetch the data between the give date range from ms sql 2005 database

[EDN Admin]

Hi,
I am developing one desktop application using vb.net 2008 and Microsoft SQL Server 2005. I need a help for fetching the data from database between a given range.

the date field in my table is of type VARCHAR(25) and the date format stored is dd-mm-yyyy. when I using the between clause in the SQL query then it is not giving all the data every time i.e. the date criteria in where clause is not working fine.
while my clients requirement is to keep the date format as dd-mm-yyyy and fetch the data according to the given range.
I search on the forum and found many queries to fetch the data by converting or type casting the date field within sql query but nothing worked.
so please help me out with that what shoud I do in this case.
The table structure that I am using is as follows

USE [Firm_master]<br/>
GO<br/>
/****** Object: Table [dbo].[tblTransaction] Script Date: 05/17/2011 21:49:11 ******/<br/>
SET ANSI_NULLS ON<br/>
GO<br/>
SET QUOTED_IDENTIFIER ON<br/>
GO<br/>
SET ANSI_PADDING ON<br/>
GO<br/>
CREATE TABLE [dbo].[tblTransaction](<br/>
[Tid] [int] IDENTITY(1,1) NOT NULL,<br/>
[Dataid] [int] NOT NULL,<br/>
[Vouch_type] [int] NULL,<br/>
[Dr_ac_name] [varchar](50) NULL,<br/>
[Cr_ac_name] [varchar](50) NULL,<br/>
[Dr_amt] [money] NULL,<br/>
[Cr_amt] [money] NULL,<br/>
[Dr_narr] [varchar](249) NULL,<br/>
[Cr_narr] [varchar](249) NULL,<br/>
[Comp_id] [int] NULL,<br/>
[date] [varchar](50) NULL,<br/>
CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED <br/>
(<br/>
[Tid] ASC<br/>
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]<br/>
) ON [PRIMARY]<br/>
<br/>
GO<br/>
SET ANSI_PADDING OFF

and the query that I am using is as follows

SELECT * from tblTransaction WHERE date between 01-04-2011 AND 30-04-2011
this query is not giving the result as expected and if I use type casting or data type conversion in query then it is not giving the correct data again sometime it is working sometime not.

if anyone can help me with this , that would be great.

thanks in advance.


View the full article