title: Verify date with SQL / PL SQL
body: <p>I am having trouble verifying dates in my code. It's not quite working how it should.</p>
  <p>The parts that are wrong have to do with this.</p>
  <pre><code>ADD_COMPLEX_SALE_TO_DB - GENERATED UNEXPECTED EXCEPTION
  ADD_COMPLEX_SALE_TO_DB - DATE NOT VALID EXCEPTION INCORRECT (WRONG MONTH)
  ADD_COMPLEX_SALE_TO_DB - DATE NOT VALID EXCEPTION INCORRECT (WRONG DAY)
  </code></pre>
  <p>My code for testing is this. I have a different block for outputting which has no issues.</p>
  <pre><code>CREATE OR REPLACE PROCEDURE ADD_COMPLEX_SALE_TO_DB (pcustid Number, pprodid Number, pqty Number, pdate Varchar2) AS
      QTY_OUTSIDE_RANGE EXCEPTION;
      STATUS_NOT_OK EXCEPTION;
      INVALID_SALE_DATE EXCEPTION;
      vCUST CUSTOMER%ROWTYPE;
      vPROD PRODUCT%ROWTYPE;
      SALEID SALE%ROWTYPE;
      vPROD_ERROR INT := 0;
  BEGIN
      IF 1 &gt; pqty OR pqty &gt; 999 THEN
      RAISE QTY_OUTSIDE_RANGE;
      END IF;
      
      SELECT * INTO vCUST
      FROM CUSTOMER
      WHERE CUSTID = pcustid;
      
      vPROD_ERROR := 1; 
      
      IF vCUST.STATUS != 'OK' THEN
      RAISE STATUS_NOT_OK;
      END IF; 
  
      SELECT * INTO vPROD
      FROM PRODUCT
      WHERE PRODID = pprodid;
      
      IF (LENGTH(pdate) &lt;&gt; 8) THEN 
          RAISE INVALID_SALE_DATE;
      END IF;
      
      INSERT INTO SALE (SALEID, CUSTID, PRODID, QTY, PRICE, SALEDATE) 
      VALUES (SALE_SEQ.NEXTVAL, pcustid, pprodid, pqty, vPROD.SELLING_PRICE, pdate); 
          
      UPD_CUST_SALESYTD_IN_DB(pcustid, (pqty*vPROD.SELLING_PRICE));   
      UPD_PROD_SALESYTD_IN_DB(pprodid, (pqty*vPROD.SELLING_PRICE));
  
  EXCEPTION
      WHEN QTY_OUTSIDE_RANGE THEN
          RAISE_APPLICATION_ERROR (-20234, 'Sale Quantity outside valid range');
      WHEN STATUS_NOT_OK THEN 
          RAISE_APPLICATION_ERROR(-20245, 'Customer status is not OK');
      WHEN INVALID_SALE_DATE THEN
          RAISE_APPLICATION_ERROR(-20252, 'Date not valid');
      WHEN NO_DATA_FOUND THEN
          IF vPROD_ERROR = 0 THEN
              RAISE_APPLICATION_ERROR (-20263, 'Customer ID not found');
          ELSE 
              RAISE_APPLICATION_ERROR (-20271, 'Product ID not found');
          END IF;
      WHEN OTHERS THEN
          RAISE_APPLICATION_ERROR(-20000, SQLERRM);
  END;
  </code></pre>
  <p>Here are the table creation statements.</p>
  <pre><code>CREATE TABLE CUSTOMER (
  CUSTID  NUMBER
  , CUSTNAME  VARCHAR2(100)
  , SALES_YTD NUMBER
  , STATUS    VARCHAR2(7)
  , PRIMARY KEY   (CUSTID) 
  );
  CREATE TABLE PRODUCT (
  PRODID  NUMBER
  , PRODNAME  VARCHAR2(100)
  , SELLING_PRICE NUMBER
  , SALES_YTD NUMBER
  , PRIMARY KEY   (PRODID)
  );
  CREATE TABLE SALE (
  SALEID  NUMBER
  , CUSTID    NUMBER
  , PRODID    NUMBER
  , QTY   NUMBER
  , PRICE NUMBER
  , SALEDATE  DATE
  , PRIMARY KEY   (SALEID)
  , FOREIGN KEY   (CUSTID) REFERENCES CUSTOMER
  , FOREIGN KEY   (PRODID) REFERENCES PRODUCT
  );
  </code></pre>
  <p>How can I go about getting testing the date is valid? It appears I need to somehow break down and test the month/day/year separately but I am unsure about how best to do that.</p>