How to get last week dates for all months SQL?

Question

Similar questions have been asked before but I could find no clear answer to this one.

How can get dates for the last week of every month in SQL? I have something like this

select date 
from date_table
where date > dateadd(day, -8, dateadd(day, -1, date_trunc('month', current_date)::date 
and date <= dateadd(day, -1, date_trunc('month', current_date)::date 

but this only gives me the dates for last month's data. Is there a way to get the last week for every month?

The date_table here is a table with every single date, along with their year month and date number etc so I also tried something like

select date 
from date_table
where day_of_month >= 22
and day_of_month < 31 

but I would want to impose some kind of condition so it works for different months (for eg: for Feb it would give me 20-28 but for Jan it would be 23-31)

Any help is appreciated! I'm pretty new to all this.

Answer 1

Hello You can test this:(MySQL)

select date 
from your_table
where 
date >= DATE_ADD(last_day(date), INTERVAL -1 WEEK) AND
date <= last_day(date)
ORDER BY date;

(Updated)Then lets test it:

Answer 2

The first query is only tp generate some dates so that you can see that it works.

But finally you use LAST_DAY(date_column) and LAST_DAY(date_column) - INTERVALm 1 WEEK to get the last 7 days of every month. as you can see in the fiddle

CREATE TABLE ti (mytime date) adn 

INSERT INTO ti
select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where gen_date between '2022-01-01' and '2022-07-31'

SELECT
mytime
FROM ti
WHERE mytime BETWEEN LAST_DAY(mytime) - INTERVAL 1 WEEK  AND LAST_DAY(mytime)

ORDER BY mytime

| mytime     |
| :--------- |
| 2022-01-24 |
| 2022-01-25 |
| 2022-01-26 |
| 2022-01-27 |
| 2022-01-28 |
| 2022-01-29 |
| 2022-01-30 |
| 2022-01-31 |
| 2022-02-21 |
| 2022-02-22 |
| 2022-02-23 |
| 2022-02-24 |
| 2022-02-25 |
| 2022-02-26 |
| 2022-02-27 |
| 2022-02-28 |
| 2022-03-24 |
| 2022-03-25 |
| 2022-03-26 |
| 2022-03-27 |
| 2022-03-28 |
| 2022-03-29 |
| 2022-03-30 |
| 2022-03-31 |
| 2022-04-23 |
| 2022-04-24 |
| 2022-04-25 |
| 2022-04-26 |
| 2022-04-27 |
| 2022-04-28 |
| 2022-04-29 |
| 2022-04-30 |
| 2022-05-24 |
| 2022-05-25 |
| 2022-05-26 |
| 2022-05-27 |
| 2022-05-28 |
| 2022-05-29 |
| 2022-05-30 |
| 2022-05-31 |
| 2022-06-23 |
| 2022-06-24 |
| 2022-06-25 |
| 2022-06-26 |
| 2022-06-27 |
| 2022-06-28 |
| 2022-06-29 |
| 2022-06-30 |
| 2022-07-24 |
| 2022-07-25 |
| 2022-07-26 |
| 2022-07-27 |
| 2022-07-28 |
| 2022-07-29 |
| 2022-07-30 |
| 2022-07-31 |

db<>fiddle here