title: How can I parse an ISO 8601 timestamp with Snowflake SQL?
body: <p>I'm looking for a generic function that allows me to parse ISO8601 timestamps. I know about <a href="https://docs.snowflake.net/manuals/sql-reference/functions/to_timestamp.html" rel="nofollow noreferrer"><code>to_timestamp_tz</code></a> but I couldn't find a way to create a <code>format</code> parameter that will parse all the possible variations of ISO-8601 datetimes:</p>
  
  <pre><code>select '2012-01-01T12:00:00+00:00'::timestamp_tz; // this works 
  
  select '2012-01-01T12:00:00+0000'::timestamp_tz; //Timestamp '2012-01-01T12:00:00+0000' is not recognized, although is a valid iso8601 (no colon in the timezone)
  
  select to_timestamp_tz('2012-01-01T12:00:00.123456+00:00', 'YYYY-MM-DDTHH24:MI:SS.FFTZH:TZM'); // works
  select to_timestamp_tz('2012-01-01T12:00:00.123456+0000', 'YYYY-MM-DDTHH24:MI:SS.FFTZH:TZM'); // Can't parse '2012-01-01T12:00:00.123456+0000' as timestamp with format 'YYYY-MM-DDTHH24:MI:SS.FFTZH:TZM', again because of it has no colon in the timezone
  
  
  select to_timestamp_tz('2012-01-01T12:00:00.123456+0000', 'YYYY-MM-DDTHH24:MI:SS.FFTZHTZM'); //works
  
  select to_timestamp_tz('2012-01-01T12:00:00.123456+00:00', 'YYYY-MM-DDTHH24:MI:SS.FFTZHTZM'); //Can't parse '2012-01-01T12:00:00.123456+00:00' as timestamp with format 'YYYY-MM-DDTHH24:MI:SS.FFTZHTZM' , fails because it doesn't expect a colon in the timezone
  
  </code></pre>
  
  <p>So is there anyway to parse a generic ISO 8601? (My input can come with different variations of ISO 8601).</p>
  
  <p>Examples input that it should parse:</p>
  
  <pre><code>2012-01-01T12:00:00.123456+00:00
  2012-01-01T12:00:00.123456+0000
  2012-01-01T12:00:00.123456+00
  2012-01-01T12:00:00.123456Z
  2012-01-01T12:00+00:00 // no seconds
  2012-01-01T12:00+0000
  2012-01-01T12:00+01
  2012-01-01T12:00Z
  
  </code></pre>
  
  <p>Mostly is reduced to handle the 4 ways of expressing the UTC offset (<code>+00:00</code>, <code>+0000</code>, <code>+00</code> and <code>Z</code>) and having optional seconds and fractional seconds.</p>