title: How to convert to date format when varchar2(7) value are being passed when there is variation?
body: <p>So I'm being passed data in varchar2(7) format that i need to convert into date format and then in insert in my trigger. I know that the function to_Date(date, 'dddyyyy') exists but there are  values that are in both dddyyyy and ddyyyy format. I've tried the following but still getting a 'day of month must be between 1 and last day of month' error. My code is below: </p>
  
  <pre><code>Insert into Submission
  (RESPONDENT_ID, SUBMISSION_DT, SUBMISSION_TYPE_ID, SUBMISSION_NAME_TX, SUBMISSION_SEQ_NB, CREATE_DT, CREATE_USER_ID, MODIFY_DT, MODIFY_USER_ID, EFFECTIVE_DT, INACTIVE_DT)
  VALUES
  ('2527451', CASE WHEN :new.data_date &gt; 99999 then To_Date(:new.data_Date, 'dddyyyy') ELSE To_Date(:new.data_Date, 'ddyyyy') END, '1', '20190418', '0', sysdate, '1', null, null, null, null);
  </code></pre>
  
  <p>sample data for data_date: </p>
  
  <pre><code>612014 3652014 012015
  </code></pre>
  
  <p>desired result: </p>
  
  <pre><code>Mar-02-2014 (3/1/YYYY for leap year), Dec-31-2014 (Dec-30 for leap year), Jan-01-2015.
  </code></pre>
  
  <p>So my question is, even with the LPAD method, there are 9 days in the calendar year that are not accounted for (1YYYY - 9YYYY to be exact). What do i do for these dates? the dates are in varchar format so I can't use a operational conditional statement (i.e. CASE WHEN :new.data_date &lt;= 10000 THEN to_date(lpad(:new.data_Date, 7, '00'), 'DDDYYYY') </p>