mysql查询两个日期的数据类型_查询以获取两个日期之间的数据（类型是varchar和formate是dd / mm / yyyy）mysql...

Hi all...

i have one table like

no Date

1 13/11/2011

2 14/11/2011

3 17/11/2011

4 02/12/2011

5 05/12/2011

and i written one query like

select * from tablname where date between '13/11/2011' and '17/11/2011'

it returns

no date

1 13/11/2011

2 14/11/2011

3 17/11/2011

when i used this query

select * from tablname where date between '13/11/2011' and '05/12/2011'

it wont return any value

because date is stored in varchar and format is dd/mm/yyyy and it is taking and comparing with first two values

soo how can i get the values in between two dates

Please can any one help me

thanks in advance

解决方案That really isn't easy: You shouldn't store dates in string fields, because it makes this problem difficult.

SQL Dates are always yyyy-MM-dd, not dd/MM/yyyy so you can't even convert these to a date type first, without using multiple SUBSTRING functions and a lot of luck.

Seriously: change your database. Store your dates in Date fields, and you can manipulate them either inside the database or outside a lot more easily, and a lot more flexibly: for example, you do not then have to concern yourself about the users locale and his date format!

"Sir,

Can You Give A Sample Query For Above Table So That I May Proceed I tried Many Ways But Am Not Getting"

It's not complex: try this to give you a clue:

SELECT CAST((SUBSTRING(Date, 7, 4) + '-' + SUBSTRING(Date, 4, 2) + '-' + SUBSTRING(Date, 1, 2)) AS Date) AS d FROM myTable

Seriously: Save yourself a LOT of future trouble by converting your database to Date or DateTime instead!

You should go with the software changes. Never keep a known bug.

If you have a better solution then do not go for some wrong.

In .net you just change all the references and database call and change the queries that's it.

Keep reading basic and start your projects with all basic known factor that may not create problem in future development.

You can fool your client but not yourself. you must learn with your own mistake and never repeat the same.

So don't afraid to make the changes in database for varchar to datetime datatype.

Just do it....Build a perfect software ;)

First of all why u stored dates as string in database..never do that

To solve ur problem, U have to first convert ur date in datetime in database and then u compare like

convert(datetime,'12/11/2008')