Date Formatting issues. TO_TIMESTAMP not formatting all rows

1

I am trying to format my dates correctly using the TO_TIMESTAMP function however it is only correctly formatting some of the dates and others are remaining null.

My text column I am trying to convert looks like Jan 31 2022 9:58AM +00:00 so I use TO_TIMESTAMP(Date_Column, ‘MON DD YYYY HH:MIPM TZO’, 1) which correctly formats the majority of the dates.
However any dates which have a single digit day are not getting formatted.
They look something like this Feb 6 2022 9:18AM +00:00.

How am I able to format this into a datetime column?

2

Hey @honour96, Welcome to the Dremio Community.

Single digit days seem to be working for me on v22.1.1

image
image1696×446 37.6 KB

Could the string be malformed some other way?

3

Hi @lenoyjacob,
Thank you for your comment.

I never did find out what was happening so its great that it works for you.

We ended up reformatting the column in the parquet file and reuploading the data with a different format which fixed the issue.

Good to know there was likely something else wrong with the string however. Thanks!