0

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. I know its not good but it was taken accidently during creation of database and I am not able to change this to date type because of lots of data in database

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

Please make this query correct if you think something wrong in it

Thank you

9
  • 2
    If we talk about March, shouldn't you use str_to_date(concat(YEAR(CURDATE()),'-03-01') rather than str_to_date(concat(YEAR(CURDATE()),'-08-01')?
    – FloT
    Sep 4 '19 at 6:29
  • You could set the log to record all queries and check the SQL that's really being run. On production only run this for a short time - it can slow down your server. Why have you hard-coded the month - should it not be just the day that's hard coded? Also, you should NOT have a field called date - it's a reserved word in SQL!! p.s. welcome to the forum! :-)
    – Vérace
    Sep 4 '19 at 7:17
  • I would also SRONGLY urge you to take the time and effort to change your date field's data type to DATE from VARCHAR!
    – Vérace
    Sep 4 '19 at 7:25
  • 1
    How much is "lots"?
    – Vérace
    Sep 4 '19 at 8:02
  • 1
    That is tiny for a table. Choose a slack period - I can't see it taking more than 15 mins at most - maybe even 15s! :-)
    – Vérace
    Sep 4 '19 at 9:41
0

The date column in database is varchar.

You should really fix that. If the string always looks like yyyy-mm-dd, then it will happen to work as a date. (And my code below will work.) That is, the string version of a DATE or DATETIME is handled almost identically the same as the proper datatype. One major difference is: "2019-08-01" versus "2019-8-1"

Do not hide a column (Date) inside a function call (str_to_date)

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

-->

WHERE b.Date >= CURDATE() - INTERVAL 1 YEAR
  AND b.Date  < CURDATE() + INTERVAL 1 DAY

Did you really want 365 days, plus today? This disagrees with your statement of "from march 2018 to march 2019". (Plus March is 03, not 08.)

Now, INDEX(Date) may help with the speed of the query. I say only "may" because the index will be ignored if the year is most of the table; it would be faster to ignore the table and simply scan the data. (And my version will be as slow as yours).

Please rearrange your query to use LEFT JOIN instead of RIGHT JOIN. My brain has trouble with RIGHT JOIN.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.