How to get last week dates for all months SQL?-CodePudding

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.

CodePudding user response：

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