title: to_date ora-01847 day of month must be between 1 and last day of month
body: <p>I am trying to execute the below query but getting this error:</p>
  
  <p><code>to_date ora-01847 day of month must be between 1 and last day of month</code></p>
  
  <p>query is to check overlaping of date and time in two different tables </p>
  
  <p><code>table1 (emp_num1 number,start_date1 date,start_time1 varchar2,end_date1 date,end_time2 varchar2)</code></p>
  
  <p><code>table2(emp_num2 number,start_date2 date,start_time2 varchar2,end_date2 date,end_time2 varchar2)</code></p>
  
  <pre><code>select *
    from table1 t1
        ,table2 t2
   where t1.emp_num 1 = t2.emp_num2
     and to_timestamp(to_char(start_date1,'DD-MON-YYYY')||' '||NVL(start_time1,'00:00'),'DD-MON-YYYY HH24:MI')
         between
        to_timestamp(to_char(start_date2,'DD-MON-YYYY')||' '||NVL(start_time2,'00:00'),'DD-MON-YYYY HH24:MI')
         and
        to_timestamp(to_char(end_date2,'DD-MON-YYYY')||' '||NVL(end_time2,'00:00'),'DD-MON-YYYY HH24:MI')
        or
        to_timestamp(to_char(end_date1,'DD-MON-YYYY')||' '||NVL(end_time1,'00:00'),'DD-MON-YYYY HH24:MI')
         between
        to_timestamp(to_char(start_date2,'DD-MON-YYYY')||' '||NVL(start_time2,'00:00'),'DD-MON-YYYY HH24:MI')
         and
        to_timestamp(to_char(end_date2,'DD-MON-YYYY')||' '||NVL(end_time2,'00:00'),'DD-MON-YYYY HH24:MI')
  </code></pre>
  
  <p>the above query resulting the error:</p>
  
  <p><code>to_date ora-01847 day of month must be between 1 and last day of month</code></p>
  
  <p>I tried running query </p>
  
  <pre><code>select to_timestamp(to_char(start_date1,'DD-MON-YYYY')||' '||NVL(start_time1,'00:00'),'DD-MON-YYYY HH24:MI') from table1
  </code></pre>
  
  <p>no error is encountered.</p>