CODEWITHSUNDEEP
phpmysql
azad chauhan
azad chauhan

Reputation: 47

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

Upvotes: 0

Views: 354

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

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

Upvotes: 1

Related Questions