0

I have the following table with date range:

create table stf
(
    d1 date,
    d2 date
);

insert into stf values
('2020-01-01','2020-12-31'),
('2020-11-25','2020-11-25'),
('2019-01-01','2020-11-29'),
('2020-11-01','2021-01-06'),
('2019-01-23','2021-06-06'),
('2019-01-20','2019-12-30');

I am looking to date range which is between current date's month and year.

Expect result should be: For current date

d1      d2
--------------------------
2020-01-01  2020-12-31
2019-01-01  2020-11-29
2020-11-01  2021-01-06
2019-01-23  2021-06-06
2020-11-25  2020-11-25

Note: The 2020-11 will fall between the above range.

Try 1:

select * 
from stf
where to_char(now(),'YYYY-MM') between to_char(d1,'YYYY-MM') and to_char(d2,'YYYY-MM');

Output:

d1      d2
--------------------------
2020-01-01  2020-12-31
2020-11-25  2020-11-25
2019-01-01  2020-11-29
2020-11-01  2021-01-06
2019-01-23  2021-06-06

Try 2:

select * 
from stf
where ((date_part('month',now()) between date_part('month',d1) and date_part('month',d2))or date_part('year',now()) between date_part('year',d1) and date_part('year',d2))

Output:

d1      d2
--------------------------
2020-01-01  2020-12-31
2020-11-25  2020-11-25
2019-01-01  2020-11-29
2020-11-01  2021-01-06
2019-01-23  2021-06-06
MAK
  • 6,824
  • 25
  • 74
  • 131

1 Answers1

1

You can just check if the start of the current month belongs to the range:

select *
from stf
where date_trunc('month', current_date) between d1 and d2
GMB
  • 216,147
  • 25
  • 84
  • 135
  • This range is missing `2020-11-25 2020-11-25`. Sorry my bad, didn't mentioned in expected result. Edited now. – MAK Nov 26 '20 at 12:39
  • I think this should be fine `to_char(now(),'YYYYMM') between to_char(d1,'YYYYMM') and to_char(d2,'YYYYMM')` right? – MAK Nov 26 '20 at 12:57
  • 1
    @MAK: yes. Or `date_trunc('month', current_date) between date_trunc('month', d1) and date_trunc('month', d2)` – GMB Nov 26 '20 at 20:34