INSTR with Date value
Use dates with character functions.
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 HIREDATE DATE);
Table created.
SQL> INSERT INTO EMP VALUES (1, 'SMITH', TO_DATE('17-DEC-1980', 'DD-MON-YYYY'));
SQL> INSERT INTO EMP VALUES (2, 'ALLEN', TO_DATE('20-FEB-1981', 'DD-MON-YYYY'));
SQL> INSERT INTO EMP VALUES (3, 'WARD', TO_DATE('22-FEB-1981', 'DD-MON-YYYY'));
SQL> INSERT INTO EMP VALUES (4, 'JONES', TO_DATE('2-APR-1981', 'DD-MON-YYYY'));
SQL> INSERT INTO EMP VALUES (5, 'MARTIN',TO_DATE('28-SEP-1981', 'DD-MON-YYYY'));
SQL> SELECT empno, INSTR(hiredate, 'FEB')
2 FROM emp;
EMPNO INSTR(HIREDATE,'FEB')
---------- ---------------------
1 0
2 4
3 4
4 0
5 0
SQL>