I would like to convert an array of string dates into two separate datetime columns.

The array will always have at least one string date, and sometimes it will have two strings dates. It will not have 3+

Extracting and parsing the first string is easy.

But the second one sometimes does not exist so I need logic to handle when it doesn’t.

Unfortunately I am getting an error with my below attempt. The error suggests to me the parseDateTime function is being run regardless of if myArray[2] actually exists, which I had hoped the if clause would prevent.

  with base as (
select ['2024-05-08 15:10:49'] as myArray
     ) 
select parseDateTime(myArray[1], '%Y-%m-%d %H:%i:%s') as dateFrom
     , if(length(myArray) > 1, parseDateTime(myArray[2], '%Y-%m-%d %H:%i:%s'), null) as dateTo
  from base

In the event of myArray only having one item I was expecting the if clause to return null.