php - MySql date range query not showing all records
问题描述
I am working on mysql query right now. I write a query from fetch data from march 2018 to march 2019. There are more than 5000 records for this year. I write a query with date range from march 2018 to march 2019 but its showing me only 150 records
One more thing here. The date column in database is varchar.
Here is the query I created
SELECT a.*,b.
FROM OrderCalculation a
RIGHT JOIN crm_order b
ON a.orderid = b.orderno
WHERE
str_to_date(b.Date,'%Y-%m-%d') >= str_to_date(concat(YEAR(CURDATE()),'-08-01'),'%Y-%m-%d')-INTERVAL 1 YEAR AND
str_to_date(b.Date,'%Y-%m-%d') <= str_to_date(CONCAT(YEAR(CURDATE()),'-08-01'),'%Y-%m-%d')
解决方案
You should not be storing dates as text. That being said, your current calls to STR_TO_DATE
are incorrect, because your date format is dd-mm-YYYY
, not YYYY-mm-dd
. Try the following query:
SELECT a.*, b.*
FROM crm_order a
LEFT JOIN OrderCalculation b
ON a.orderid = b.orderno
WHERE
STR_TO_DATE(b.Date, '%d-%m-%Y') >= '2018-03-01' AND
STR_TO_DATE(b.Date, '%d-%m-%Y') < '2019-04-01';
If instead you want all data for the past year before the first of the current month, then try:
SELECT a.*, b.*
FROM crm_order a
LEFT JOIN OrderCalculation b
ON a.orderid = b.orderno
WHERE
STR_TO_DATE(b.Date, '%d-%m-%Y') >= DATE_FORMAT(NOW() ,'%Y-%m-01') - INTERVAL 1 YEAR AND
STR_TO_DATE(b.Date, '%d-%m-%Y') < DATE_FORMAT(NOW() ,'%Y-%m-01');
推荐阅读
- reactjs - 如何在材质 UI 中使用 withStyles 覆盖引导类
- excel - 索引匹配 与多个条件匹配
- html - 从 HTTP 迁移到 HTTPS
- ms-access - 通过复制现有表的结构创建 Access 表
- javascript - 连续加载localhost:3000
- reactjs - “错误:您试图在一个本应不可变且已被冻结的对象上设置键 `latitude` 的值为 `37.785834`。”
- node.js - 是否有任何功能可以为集合中的所有现有 mongodb 对象添加新属性
- javascript - 如何检查 Cloud Firestore 集合中的所有文档中是否存在字段值?
- java - Spring MVC 从 JSP 中的表单在 @Controller 中创建一个对象
- python - 使用 numpy 摆脱循环