NUMTOYMINTERVAL - Subtracting 18 years

[missmis44]

I am trying to select from a Query that I created a count of the inmates that were over 17 when they were booked. I am getting the following Oracle Error:
DECLARE
*
ERROR at line 1:
ORA-01839: date not valid for month specified
ORA-06512: at line 18

SELECT COUNT(BOOK_ID) INTO ADULT_MALE
FROM CJI_BOOK_RELEASE
WHERE POP_DATE
BETWEEN BOOKDATE AND RELEASEDATE AND SEX='M' AND TO_CHAR(DOB, 'MM/DD/YYYY') >= TO_CHAR(POP_DATE + NUMTOYMINTERVAL(-18, 'YEAR'),'MM/DD/YYYY');

Below is my entire script:
CREATE OR REPLACE VIEW CJI_BOOK_RELEASE AS
SELECT J.BOOK_ID, J.BOOKDATE, R.RELEASTIME,
NVL2(R.RELEASTIME, R.RELEASTIME, SYSTIMESTAMP) RELEASEDATE,N.SEX, N.RACE, N.DOB, J.BKFACILITY, J.CHRGDESC, NVL2(J.CJI_RENTAL_STATUS, J.CJI_RENTAL_STATUS, 'LOCAL') RENTAL_STATUS,
R.RELEASETO, R.RELSREASON, R.CJI_RELEASE_AGENCY
FROM JRELEASE R
RIGHT OUTER JOIN JMMAIN J
ON (R.BOOK_ID = J.BOOK_ID),NMMAIN N
WHERE N.NAME_ID = J.NAME_ID;

/*CREATES TABLE TO TALLY THE DAILY COUNT OF MALES AND FEMALES AT 8 AM AND FOR ALL INMATES IN
CUSTODY AT ANY POINT IN TIME DURING EACH DAY*/

CREATE TABLE CJI_DAILY_POPULATION(
POPULATION_DATE DATE NOT NULL,
POPULATION NUMBER (10),
ADULT_MALE NUMBER (10),
JUVENILE_MALE NUMBER (10),
ADULT_FEMALE NUMBER (10),
JUVENILE_FEMALE NUMBER (10)
);

DECLARE
POP_DATE DATE := TO_DATE('01/01/1999 08:00:00', 'MM/DD/YYYY HH24:MI:SS');
POPULATION NUMBER (10);
ADULT_MALE NUMBER (10);
JUVENILE_MALE NUMBER (10);
ADULT_FEMALE NUMBER (10);
JUVENILE_FEMALE NUMBER (10);

BEGIN
WHILE POP_DATE < SYSDATE
LOOP
/*POPULATION*/
SELECT COUNT(BOOK_ID) INTO POPULATION
FROM CJI_BOOK_RELEASE
WHERE POP_DATE
BETWEEN BOOKDATE AND RELEASEDATE;
/*ADULT_MALE*/
SELECT COUNT(BOOK_ID) INTO ADULT_MALE
FROM CJI_BOOK_RELEASE
WHERE POP_DATE
BETWEEN BOOKDATE AND RELEASEDATE AND SEX='M' AND TO_CHAR(DOB, 'MM/DD/YYYY') >= TO_CHAR(POP_DATE + NUMTOYMINTERVAL(-18, 'YEAR'),'MM/DD/YYYY');
/*JUVENILE_MALE*/
SELECT COUNT(BOOK_ID) INTO JUVENILE_MALE
FROM CJI_BOOK_RELEASE
WHERE TRUNC(POP_DATE)
BETWEEN BOOKDATE AND RELEASEDATE AND SEX='M' AND
TO_CHAR(DOB, 'MM/DD/YYYY') >= TO_CHAR(POP_DATE + NUMTOYMINTERVAL(-18, 'YEAR'),'MM/DD/YYYY');
/*ADULT_FEMALE*/
SELECT COUNT(BOOK_ID) INTO ADULT_FEMALE
FROM CJI_BOOK_RELEASE
WHERE TRUNC(POP_DATE)
BETWEEN BOOKDATE AND RELEASEDATE AND SEX='F' AND
TO_CHAR(DOB, 'MM/DD/YYYY') >= TO_CHAR(POP_DATE + NUMTOYMINTERVAL(-18, 'YEAR'),'MM/DD/YYYY');
/*JUVENILE_FEMALE*/
SELECT COUNT(BOOK_ID) INTO JUVENILE_FEMALE
FROM CJI_BOOK_RELEASE
WHERE TRUNC(POP_DATE)
BETWEEN BOOKDATE AND RELEASEDATE AND SEX='F' AND
TO_CHAR(DOB, 'MM/DD/YYYY') >= TO_CHAR(POP_DATE + NUMTOYMINTERVAL(-18, 'YEAR'),'MM/DD/YYYY');

INSERT INTO CJI_DAILY_POPULATION VALUES (POP_DATE, POPULATION, ADULT_MALE, JUVENILE_MALE,
ADULT_FEMALE,JUVENILE_FEMALE);
POP_DATE := POP_DATE + 1;
END LOOP;
END;

 

[BJCooperIT]

Firstly, you cannot properly compare a character string in 'MM/DD/YYYY' format for dates, otherwise:
11/08/2006 would be less than
12/25/1968 because 11 is less than 12. The comparison must be date to date.

Secondly, I suspect you are encountering this problem with NUMTOYMINTERVAL.

Try instead:

SELECT COUNT(book_id)
INTO   adult_male
FROM   cji_book_release
WHERE  pop_date BETWEEN bookdate AND releasedate
AND    sex = 'M'
AND    dob >= add_months(pop_date,(18*-12));

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com