Sql query to select Date between the range

[pavanip]

Hi, I am having database table as TicketNo,Name,Issue Date. I inserted the data for "IssueDate" field using calender control in Asp.Net. So the table data looks like this TicketNo Name IssueDate 1 xxx Feb 5 2009 12:00AM 2 yyy Feb 6 2009 12:00AM 3 zzz Feb 10 2009 12:00AM 4 aaa Feb 15 2009 12:00AM Now i want to write the query to select "IssueDate" between 02/04/2009 and 02/11/2009. Please Can anybody help me how to write query to retrieve the data between those dates? Thanks Pavani

[Mycroft Holmes]

pavanip wrote:

select "IssueDate" between 02/04/2009 and 02/11/2009.

almost had it in your question Select * from Tablename where IssueDate Between '02/04/2009' and '02/11/2009'

Never underestimate the power of human stupidity RAH

[Rupesh Kumar Swami]

select * from table where issueDate BETWEEN CONVERT(varchar, '2009-02-04', 111) AND CONVERT(varchar, '2009-02-11', 111)

Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

[pavanip]

I tried with that query but when i execute that query i am getting zero results but there are records in my table with that dates. I stored date value in varchar is it correct or i have to use only datetime datatype.

[pavanip]

I tried with this query select * from table where issueDate BETWEEN CONVERT(varchar, '2009-02-04', 111) AND CONVERT(varchar, '2009-02-11', 111) but when i execute that query i am getting zero results but there are records in my table with that dates. I stored date value in varchar is it correct or i have to use only datetime datatype.

[J4amieC]

You need to store the date as a date. Why people store dates in a character field is totally beyond me.

[Mycroft Holmes]

As Jamie said, mistake 1 STORE YOUR DATES AS DATE TIME. So now that your have screwed your data you have 2 choices. Convert the dates from varchar to datetime - this is the recommended solution Make all the date values into datetime in your query. Select * from table name where convert(datetime,Stupiddate) between thisdate and thathdate

Never underestimate the power of human stupidity RAH

[Mycroft Holmes]

Rupesh Wrong way - you are converting to varchar and comparing strings, you need to convert to datetime to use between!

Never underestimate the power of human stupidity RAH

[pavanip]

Thanks for your response that query is working for my condition. Select * from tbl_ticketinfo where convert(datetime,issuedate) between '" 02/05/2009' and '02/09/2009'