-1

This is my SQL to fetch orders of current week:

SELECT * FROM orders 
WHERE YEARWEEK(order_date, 1) = YEARWEEK(CURDATE(), 1) 
ORDER BY order_date DESC;

But the problem is that it also selects records of future dates in the current week. How to stop that?

Sachin
  • 1,646
  • 3
  • 22
  • 59

2 Answers2

3

Just add a condition on the current date as well:

SELECT o.*
FROM orders o
WHERE YEARWEEK(order_date, 1) = YEARWEEK(CURDATE(), 1) AND
      order_date <= CURDATE()
ORDER BY order_date DESC;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Be aware: if you use the order_date column in a function to build the where clause, this will result in a query which does not use an index on order_date and so should be avoided if your table can be big.

If you calculate the first date of the current week you can build your where clause just using order_date and it will use an index.

Maybe this can help to inspire you:

WHERE order_date BETWEEN DATEADD(DAY, -WEEKDAY(CURDATE())), CURDATE()) AND CURDATE()

I haven't tested this but adapted the function names to MySQL using the documentation. The idea is to calculate the first date of the current week so you know the range to filter.

Conffusion
  • 4,335
  • 2
  • 16
  • 28
  • Gordon Linoff's solution is easier and better than yours. And don't understand why this question is marked -ve... – Sachin Aug 07 '20 at 14:24
  • yes it is easier but I'm not so sure about better: it will not use an index on order_date. Good luck. So as your database grows this can become a performance timebomb. – Conffusion Aug 07 '20 at 14:26
  • Conffusion I already knew that you will point out towards "better" word but I still used it. FYI index makes INSERT, UPDATE, etc. queries slower. I am not planing to use index anyway. – Sachin Aug 07 '20 at 15:26
  • @Sachin If you expect your table will remain small enough to not require indexes, fine by me. No judgment. I wanted to make this comment for other developers arriving on this page and looking for a solution that also works in larger tables. – Conffusion Aug 07 '20 at 15:35
  • Conffusion I understand what you said but as we have read at several places that index slows down your queries. So please tell me that why it is written so? – Sachin Aug 07 '20 at 18:31
  • indexes don't slow down your select queries. It is the reason why they exist. Indeed, during insert and update there is an overhead because the indexes need to be updated as well. "all invoices of customerId X" and you have several million invoices you will be convinced easily to define an index on customerId. Read the introduction on https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html – Conffusion Aug 10 '20 at 07:31