Conversion and dates and nulls (UK format)

1

I have Dremio reading a Tilde delimited file.

If I have two rows in that file as

Paul~31/12/2009~29/09/2021
Jim~~29/09/2021

ISDATE returns FALSE - when it IS a date, but there appears to be no documented way to handle this UK format?

Now the UI utilises TO_DATE(EffectiveDate, ‘DD/MM/YYYY’, 1) , but I assume there is a reason this isn’t in the documentation? This seems to handle the empty text scenario/invalid date scenarion

So, when the column in the source file is empty (see row 2, column 2), without using TO_DATE, how can I achieve what TO_DATE does?

2

@spireite When CAST is used, it assumes the default format of date, so TO_DATE would the best choice, when you say anything other than TO_DATE, what functions do you want to use?

3

I don’t have a problem using TO_DATE in itself, it was more a question of why the function isn’t actually in the documentation (Dremio) which made me wonder if it should be used at all