How do I get specific days of te month in SQL?

condexter 0
Viewed 56

For example I have data in interval from '2020-01-01' to '2021-01-31' how can I get specific days from this months like first 2 days and last 3 days for each month.

Result I want:

date col1
'2020-01-01' x
'2020-01-02' x
'2020-01-29' x
'2020-01-30' x
'2020-01-31' x
'2020-02-01' x
'2020-02-02' x
'2020-02-26' x
'2020-02-27' x
'2020-02-28' x
'2020-03-01' x
'2020-03-02' x
'2020-03-29' x
'2020-03-30' x
'2020-03-31' x

et cetera

sql postgresql
1 Answers

A date truncated to the first day of the month plus one month minus one day is the last day of that month. So

select * from the_table 
where 
 extract('day' from "date") in (1, 2) -- the first two days of the month
 or "date" between -- the last 3 days of the month
  (date_trunc('month', "date") + interval '1 month - 3 days')::date and
  (date_trunc('month', "date") + interval '1 month - 1 days')::date;
Stefanov.sm0
Related