title: Date Criteria in PS Query
body: <p>I have this criteria for my PS Query:</p>
  
  <p><em>Admission Data Creation (Date)
  This is a FROM date. All records starting from the selected date to the current date will be included in the report. Leaving this blank will default this to the current date.</em> </p>
  
  <p>And I made the query below:</p>
  
  <pre><code>  WHERE ( ( A.EMPLID = B.EMPLID 
       AND A.EMPLID = E.EMPLID 
       AND H.EFFDT = 
          (SELECT MAX(H_ED.EFFDT) FROM PS_ACAD_PROG_CAF H_ED 
          WHERE H.INSTITUTION = H_ED.INSTITUTION 
            AND H.ACAD_PROG = H_ED.ACAD_PROG 
            AND H_ED.EFFDT &lt;= SYSDATE) 
       AND A.EMPLID = C.EMPLID 
       AND C.INSTITUTION = A.INSTITUTION 
       AND C.INSTITUTION = D.INSTITUTION 
       AND C.SAD_UC_APPCODE = D.SAD_UC_APPCODE 
       AND C.SAD_UC_PERS_ID = D.SAD_UC_PERS_ID 
       AND C.INSTITUTION = F.INSTITUTION 
       AND C.SAD_UC_APPCODE = F.SAD_UC_APPCODE 
       AND C.SAD_UC_PERS_ID = F.SAD_UC_PERS_ID 
       AND F.EFFDT = 
          (SELECT MAX(F_ED.EFFDT) FROM PS_UM_CTXT_PERCTXT F_ED 
          WHERE F.INSTITUTION = F_ED.INSTITUTION 
            AND F.SAD_UC_APPCODE = F_ED.SAD_UC_APPCODE 
            AND F.SAD_UC_PERS_ID = F_ED.SAD_UC_PERS_ID 
            AND F_ED.EFFDT &lt;= SYSDATE) 
      AND F.EFFSEQ = 
          (SELECT MAX(F_ES.EFFSEQ) FROM PS_UM_CTXT_PERCTXT F_ES 
          WHERE F.INSTITUTION = F_ES.INSTITUTION 
            AND F.SAD_UC_APPCODE = F_ES.SAD_UC_APPCODE 
            AND F.SAD_UC_PERS_ID = F_ES.SAD_UC_PERS_ID 
            AND F.EFFDT = F_ES.EFFDT) 
       AND D.INSTITUTION = G.INSTITUTION 
       AND D.SAD_UC_PERS_ID = G.SAD_UC_PERS_ID 
       AND D.SAD_UC_APPCODE = G.SAD_UC_APPCODE 
       AND D.SAD_UC_CHOICENO = G.SAD_UC_CHOICENO 
       AND H.SCC_CAF_ATTR_SEQ = 1 
       AND H.SCC_CAF_ATTRIB_NM = 'UM_MDAS_MAP' 
       AND H.SCC_CAF_ATTR_VAL IN ('MAP','MDAS') 
       AND G.SAD_UC_ABBREVCODE = 'AC' 
       AND F.UM_CTXT_WP_FLAG = 'Y' 
       AND F.UM_CTXT_WPPLS_FLAG = 'Y' 
       AND A.INSTITUTION = :1 
       AND ( A.ACAD_CAREER = :2 
       OR ' ' = :2) 
       AND 1 = 1 AND (  A.EMPLID = :4
  OR ' ' = :4) 
       AND ( TO_DATE(:3,'YYYY-MM-DD') IS NULL 
       OR A.ADM_CREATION_DT &gt;= 
       TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) 
       AND A.ADM_CREATION_DT &lt;= 
       TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') )) 
    ORDER BY 1, 5, 2, 6
  </code></pre>
  
  <p>The part of the criteria above if you can see is this one: </p>
  
  <pre><code>     AND ( TO_DATE(:3,'YYYY-MM-DD') IS NULL 
       OR A.ADM_CREATION_DT &gt;= 
       TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) 
       AND A.ADM_CREATION_DT &lt;= 
       TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') )) 
  </code></pre>
  
  <p>The problem is it just doesn't pull what I want, I tried different ways of doing this but gets something like a date format error. Been with this problem since last week. Really need help on how to add the criteria to my PS Query.</p>