在mysql中存储在varchar中的日期之间搜索数据 [英] searching data between dates stored in varchar in mysql

问题描述

我在 varchar 中以 dd / mm / yyyy 格式在server_date_time列中存储我的日期，获取位于某些日期之间的记录，以便我使用查询

I am storing my dates in column server_date_time in varchar in dd/mm/yyyy format and i want to fetch the records lying between some dates so i have used the query

    select * from activity_emp 
where date_format(str_to_date(substr(server_date_time,1,10),'%d/%m/%Y'),'%d/%m/%Y')>=
'29/09/2012'
    and date_format(str_to_date(substr(server_date_time,1,10),'%d/%m/%Y'),'%d/%m/%Y')<=
'07/10/2012';

我已将 varchar 转换为查询中的字符串但我的查询返回的查询数据只与29/09/2012和30/09/2012相关。它也应该返回10月份的查询

I have converted varchar to string in query but my query return query data only related to 29/09/2012 and 30/09/2012. It should also return query for the month of October

推荐答案

尝试这个。您可以按照您的问题以dd / mm / yyyy格式输入日期...

Try with this. You can input date in dd/mm/yyyy format as in your question...

SELECT * FROM activity_emp
WHERE STR_TO_DATE(server_date_time, '%d/%m/%Y')
  BETWEEN STR_TO_DATE('29/08/2012', '%d/%m/%Y')
    AND STR_TO_DATE('07/10/2012', '%d/%m/%Y')

更新：我强烈建议您将数据类型从 VARCHAR 更改为 DATETIME

Update: I strongly recommend you to change datatype from VARCHAR to DATETIME

干杯!!!

这篇关于在mysql中存储在varchar中的日期之间搜索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！