Issues with casting inconsistent dates with Apache Drill
<p>I have list of dates which are unfortunately inconsistent: </p>

<pre><code>3/09/2015 15:20:03
29/04/2014
06/03/2012
31/12/2015 06:29:40
13/05/2014
20/02/2010
01/10/2015 23:46:12
17/11/2014
30/10/2015 22:28:00
14/02/2014
</code></pre>

<p>I'm trying to cast these objects into a consistent format: date or timestamp but cannot do so with something like this:</p>

<pre><code> SELECT TO_TIMESTAMP(DATE_OF_BIRTH, 'dd/MM/yyyy HH:mm:ss') FROM PERSON
</code></pre>

<p>or</p>

<pre><code> SELECT TO_DATE(DATE_OF_BIRTH, 'dd/MM/yyyy') FROM PERSON 
</code></pre>

<p>The resulting error is shown. </p>

<pre><code>UserRemoteException: SYSTEM ERROR: IllegalArgumentException: I nvalid format: "13/09/2015 15:20:03" is malformed at " 15:20:03" Fragment 0:0
</code></pre>

<p>How can I get drill to accept multiple formats for conversion. </p>