Search code examples
sqlmysqlwhere-clausedate-arithmetic

default the end date based on previous week's last date

How do I go about making the in_date's end date always based on the previous week's last date? For example, today is 24 April 2023, and the previous week's (Mon to Sun) last date was 16/04/2023

Expected SQL Query:

SELECT ga.in_date,
        FROM in_txn ga
        LEFT JOIN cntr_mst cm ON cm.cntr_num = ga.cntr_num
        WHERE status_active = 'A'
        and (in_date between '2020-01-01 00:00:00' and '2023-4-16 23:59:59')
Solution

  • You want to filter from the beginning of year 2020 to the end of last week.

    We can compute the date of the last Monday (or today if it’s a Monday) with current_date and weekday():

    current_date - interval weekday(current_date) day

    And we can use this expression to filter with a half-open interval, voilà!

    WHERE status_active = 'A' 
  AND in_date >= '2020-01-01'
  AND in_date <  current_date - interval weekday(current_date) day