title: compare month and year in SQL
body: <p>I want to compare month and year columns in my table with month and year parameters provided by user I used to put this expression</p>
  
  <pre><code> select  ca_expense_mst.trans_id, trans_no
   from ca_expense_mst , CA_EXPENSE_FINANCE_DTL fndDtl     
   where  ca_expense_mst.TRANS_ID=fndDtl.TRANS_ID    
   and TO_DATE(''|| fndDtl.year ||'/'|| fndDtl.month || '/01', 'yyyy/mm/dd')  between  TO_DATE(''|| :fromYear ||'/'|| :fromMonth || '/01', 'yyyy/mm/dd')  and TO_DATE(''|| :toYear ||'/'|| :toMonth || '/01', 'yyyy/mm/dd')     
   and  fndDtl.account_id= nvl(to_number(:account_no),fndDtl.account_id)
  </code></pre>
  
  <p>it works fine but if I add the aggregate function <code>count()</code> I get this exception:</p>
  
  <blockquote>
    <p>Caused by: java.sql.SQLDataException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0</p>
  </blockquote>
  
  <p>I need a method so that I can compare moth and year column with month and year parameters</p>
  
  <p>this is the SQL that throws the exception:</p>
  
  <pre><code>select  count(ca_expense_mst.trans_id)  as all_trans
  from ca_expense_mst , CA_EXPENSE_FINANCE_DTL fndDtl     
  where  ca_expense_mst.TRANS_ID=fndDtl.TRANS_ID    
  and TO_DATE(''|| fndDtl.year ||'/'|| fndDtl.month || '/01', 'yyyy/mm/dd')  between  TO_DATE(''|| 
  :fromYear ||'/'|| :fromMonth || '/01', 'yyyy/mm/dd')  and TO_DATE(''|| :toYear ||'/'|| :toMonth || '/01', 'yyyy/mm/dd') 
  and  fndDtl.account_id= nvl(to_number(:account_no),fndDtl.account_id)
  </code></pre>
  
  <p>here is the script of my table</p>
  
  <pre><code>CREATE TABLE SAS_ADF.CA_EXPENSE_FINANCE_DTL
  (
    EXP_FIN_ID          NUMBER,
    ACCOUNT_ID          NUMBER,
    TRANS_ID            NUMBER,
    AMOUNT_BY_CD        NUMBER,
    DEBIT_CREDIT        VARCHAR2(2 BYTE),
    CHEQUE_STATUS       VARCHAR2(20 BYTE),
    REFERENCE_NO        VARCHAR2(500 BYTE),
    REFERENCE_DATE      DATE,
    ENT_BY              VARCHAR2(50 BYTE),
    ENT_DATE            DATE,
    MODIFY_BY           VARCHAR2(50 BYTE),
    MODIFY_DATE         DATE,
    REFUND_TYPE_ID      NUMBER,
    STUDENT_ID          NUMBER,
    STD_FIN_APP_FLAG    VARCHAR2(2 BYTE),
    STD_AUDIT_APP_FLAG  VARCHAR2(2 BYTE),
    ACTION_DATE         DATE,
    MONTH               NUMBER,
    YEAR                NUMBER,
    CANCEL_FLAG         VARCHAR2(2 BYTE),
    EXP_FIN_ID_M        NUMBER,
    USER_TRANS_ID       VARCHAR2(500 BYTE),
    OLD_STUDENT_ID      NUMBER,
    CD                  VARCHAR2(10 BYTE),
    CURRENCY_RATE       NUMBER,
    AMOUNT_BY_CD_L      NUMBER,
    CURR_ISO            VARCHAR2(4 BYTE),
    STDUENT_ID_W        NUMBER,
    AMOUNT_BY_CD_C      NUMBER,
    DEBIT_CREDIT_C      VARCHAR2(10 BYTE)
  )
  </code></pre>
  
  <p>and here is screen shot of some data</p>
  
  <p><img src="https://i.stack.imgur.com/iqHwx.png" alt="enter image description here"></p>