How to query data between two specific months for all years

oracle

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

Best Answer

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

Related Question