How to query data between two specific months for all years?

Question

Need to query the data between two months of all years.

For Example:

Table 1 --> contains 100 records for 10 years

Column --> Created date

Need to pull the data between April to May for all years

Answer 1

You can use EXTRACT (MONTH FROM DATE) function.

CREATE TABLE TABLE1(ID INT, CREATED_DATE DATE);
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180101', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180201', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180301', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180401', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20170401', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180501', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180601', 'YYYYMMDD'));

SELECT *
FROM   TABLE1
WHERE  EXTRACT (MONTH FROM CREATED_DATE) >= 4
AND    EXTRACT (MONTH FROM CREATED_DATE) <= 5;

ID | CREATED_DATE
-: | :-----------
 1 | 01-APR-18   
 1 | 01-APR-17   
 1 | 01-MAY-18   

dbfiddle here

Answer 2

Select *
from T
where extract(month from date_column) between :start_month_number and :end_month_number;

This won't work across YEAR boundaries.