Question 20368 in Mysql Question posted in
The official MySQL documentation can be found here.

Exclude two last week mysql

Good morning,

I am on a MySQL database and I have the following query which extracts data from the last 6 months to today’s date :

select distinct
            date_format(abc.date,'%d/%m/%Y') as date_bes
 from table abc
 where   abc.date BETWEEN CURDATE() - INTERVAL 6 MONTH AND CURDATE() - INTERVAL  2 WEEK

I want to change my query to the last 6 months, excluding the last current week.

For example I want to exclude week 11 which is from March 13 to March 19
and week 12 which is the current week: from March 20 to 26.

Thanks in advance 🙂

Tags:

2

Answers


  1. 0

    You can do it as follows :

    select distinct
            date_format(abc.date,'%d/%m/%Y') as date_bes
 from table abc
 where   abc.date BETWEEN CURDATE() - INTERVAL 6 MONTH AND DATE_ADD(CURDATE(), INTERVAL(1-DAYOFWEEK(CURDATE())) DAY) - INTERVAL 1 WEEK

    to get the first day of the week starting from sunday : DATE_ADD(CURDATE(), INTERVAL(1-DAYOFWEEK(CURDATE())) DAY)

    to get the first day of the week starting from monday : DATE_ADD(CURDATE(), INTERVAL(-WEEKDAY(CURDATE())) DAY);

    In this case we need to get data till sunday 2023-03-12

    Login or Signup to reply.
  2. 0

    You can use WEEKDAY() which:

    Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

    We use +1 to go back to the Sunday, and another +7 to go back another week.

    SELECT DISTINCT DATE_FORMAT(abc.date,'%d/%m/%Y') AS date_bes
FROM table abc
WHERE abc.date BETWEEN CURDATE() - INTERVAL 6 MONTH
                   AND CURDATE() - INTERVAL (WEEKDAY(CURDATE()) + 8) DAY

    This solution and the one provided by SelVazi provide slightly different results. I am not sure which you want so here is a table showing the differences:

    CURDATE() this one SelVazi
    2023-03-18 2023-03-05 2023-03-05
    2023-03-19 2023-03-05 2023-03-12
    2023-03-20 2023-03-12 2023-03-12
    2023-03-21 2023-03-12 2023-03-12
    2023-03-22 2023-03-12 2023-03-12
    2023-03-23 2023-03-12 2023-03-12
    2023-03-24 2023-03-12 2023-03-12
    2023-03-25 2023-03-12 2023-03-12
    2023-03-26 2023-03-12 2023-03-19
    2023-03-27 2023-03-19 2023-03-19
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search