查询以获取两个日期之间的数据（类型是varchar和formate是dd / mm / yyyy）mysql [英] Query to get data in between two dates in (type is varchar and formate is dd/mm/yyyy) mysql

问题描述

大家好......

i有一张桌子像

没有日期

1 13/11/2011

2 14/11/2011

3 17/11/2011

4 02/12/2011

5 05/12/2011

和我写了一个查询，例如

select * from tablname，其中'13 / 11/2011'和'17 / 11/2011之间的日期'

它返回

没有约会

1 13/11/2011

2 14/11/2011

3 17/11/2011
当我使用此查询时


从tablname中选择*，其中'13 / 11/2011'和'05 / 12/2011'之间的日期

它不会返回任何值

因为日期存储在varchar中，格式是dd / mm / yyyy并且正在接受并与前两个值进行比较

soo我如何获得两个日期之间的价值

请任何人帮助我

提前感谢

解决方案

这真的不容易：你不应该在字符串字段中存储日期，因为它会产生这个问题很难。

SQL日期总是yyyy-MM-dd，而不是dd / MM / yyyy所以你甚至不能先将它们转换为日期类型，而不使用多个SUBSTRING函数和很多运气。



说真的：改变数据库。将日期存储在日期字段中，您可以在数据库内部或外部更轻松地操作它们，也可以更灵活地操作它们：例如，您不必关心用户区域设置和日期格式！



先生，

你可以给上面的表格提供样本查询以便我可以继续我尝试了很多方法但是没有得到



这并不复杂：试试这个给你一个线索：

  SELECT  CAST（（SUBSTRING（日期， 7 ， 4 ）+ '   - ' + SUBSTRING（日期， 4 ， 2  ）+ '   -   + SUBSTRING（日期， 1 ， 2 ）） AS  日期） AS  d  FROM  myTable 

说真的：通过将数据库转换为Date或DateTime来节省很多未来的麻烦！

你应该进行软件更改。永远不要留下已知的错误。



如果你有更好的解决方案，那就不要错。



在.net中，您只需更改所有引用和数据库调用，并更改其查询。



继续阅读基础并开始你的项目，所有基本的已知因素可能不会在未来的发展中产生问题。



你可以欺骗你的客户而不是你自己。你必须学习你自己的错误，不要重复同样的事情。



所以不要害怕在varchar到datetime数据类型的数据库中进行更改。



只需这样做......构建一个完美的软件;）

首先为什么你把日期存储为字符串in数据库..无论如何这样做

要解决你的问题，你必须首先在数据库中的datetime中转换你的日期，然后你比较像

 convert（datetime，'  12/11/2008'）

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')

这篇关于查询以获取两个日期之间的数据（类型是varchar和formate是dd / mm / yyyy）mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！