title: Why does Oracle strip leading 0s from fractional seconds?
body: <p>I'm validating some batch input (i.e. plain text); the first stage of the validation is to ensure that the column I have to order by is actually in the correct <code>timestamp</code> format before trying to place it into my timestamp. In this case <code>'yyyy/mm/dd hh24:mi:ss:ff2'</code>.</p>
  
  <p>However, it appears as though Oracle removes leading 0s from the fractional seconds precision of a timestamp format. For instance <code>009</code> is assumed to be a precision of 2 (or less), as is <code>0099</code>, but not <code>0090</code>. The first two examples are, obviously, incorrect. It appears as though, for the purposes of a date-time format model, the fractional seconds precision is the precision, excluding leading 0s.</p>
  
  <p>The behaviour seems to occur whatever the precision.</p>
  
  <h3>This example is correct:</h3>
  
  <pre><code>select to_timestamp('2012/06/20 05:12:41:91','yyyy/mm/dd hh24:mi:ss:ff2') t
    from dual;
  
  T
  ---------------------------------------------------------------------------
  20-JUN-12 05.12.41.910000000
  </code></pre>
  
  <h3>These examples are incorrect:</h3>
  
  <p>I would expect an error but could deal with it being truncated.</p>
  
  <pre><code>select to_timestamp('2012/06/20 05:12:41:091','yyyy/mm/dd hh24:mi:ss:ff2') t
    from dual;
  
   T
  ---------------------------------------------------------------------------
  20-JUN-12 05.12.41.091000000
  
  select to_timestamp('2012/06/20 05:12:41:0091','yyyy/mm/dd hh24:mi:ss:ff2') t
    from dual;
  
  T
  ---------------------------------------------------------------------------
  20-JUN-12 05.12.41.009100000
  
  select to_timestamp('2012/06/20 05:12:41:00091','yyyy/mm/dd hh24:mi:ss:ff2') t
    from dual;
  
  T
  ---------------------------------------------------------------------------
  20-JUN-12 05.12.41.000910000
  </code></pre>
  
  <h3>This error is correct; it has a fractional seconds precision of 3.</h3>
  
  <pre><code>select to_timestamp('2012/06/20 05:12:41:901','yyyy/mm/dd hh24:mi:ss:ff2') t
    from dual;
  select to_timestamp('2012/06/20 05:12:41:901','yyyy/mm/dd hh24:mi:ss:ff2') t
                      *
  ERROR at line 1:
  ORA-01880: the fractional seconds must be between 0 and 999999999
  </code></pre>
  
  <p>I'm using release 11.2.0.1.0 but this behaviour also appears in 11.1.0.6.0 and 9.2.0.1.0 so it's obviously been around for a while.</p>
  
  <p>Is this a "feature" I was previously unaware of?</p>
  
  <p>The solution seems to be assuming that all the timestamps have a precision of 6 digits but is there another that can actually validate the the data I've been given is correct?</p>