显示特定周期间创建的sql记录

Question

I have tons of records that are in my database table leadactivity basically I need to display all the records that were created in the first week of the current month, then also another query to display records in the second week, same for third week and then finally the fourth week of the current month.

I have a column called created_date which onupdate puts in the current timestamp

What is the best way to achieve this?

Answer 1

You can use date functions for this:

select la.*
from leadactivity la
where day(la.created_date) between 1 and 7 and
      created_date >= curdate() + (1 - day(curdate())) day;

The above assumes MySQL syntax. Most databases have similar functionality, although the specific functions may differ.