Unable to convert string data type to date data type

Hello There,

I have a table in Quicksight dataset that was imported from Snowflake. The table filed contains the data “2023-02-14T00:00:00.000Z” in this format and data type is string.

I was trying to convert this data type to “date” but getting error message no matter what I tried. I have verified that field dosen’t have any null value.

Can you please please let me know, how I can change this field to Date from string.

Any help would be greatly appreciated!

Thanks

Tried:
yyyy-MM-ddTHH:mm:ss.SSS
yyyy-MM-dd’T’HH:mm:ss.SSSz
yyyy-MM-dd’T’HH:mm:ss.SSSZ
yyyy-MM-dd’T’HH:mm:ss.SSS’Z’
yyyy-MM-dd’T’HH:mm:ss.SSS’z’

Getting this error:

Tried this:
yyyy-MM-dd’T’HH:mm:ss.SSS

Getting these two diffrent error:


I also tried to create a calculated field by using parseDate formula, but no luck.

First of all, there is a list of supported date formats when going from string to date. If you are using one of those and there are no format exceptions on your raw data then it should work.

If you are using a supported date format and it is still not working, I think it is most likely because you have exceptions on your data. Your best shot will be using a calculated field (parseDate). However, parseDate will also not work if you have exceptions in your string format.

So I believe you have two options. Option number one is to convert all the exceptions to the right string format by manipulating the string through a calculated field before you use parseDate.

Option number two is two detect the data points that are not in the right format and make them become an empty string. parseDate should work then.

I hope this helps

1 Like

Hi @mateoleon210,

Thank you for the reply and I appreciate the help.

Can you show me “with an example” how I can try the two options that you suggested please?

Thankyou

Hi @mateoleon210,

Similar to your “option-1” suggestion!
So, I created a calculated field by using substring and extracted the value from 1-10. Then parseDate’ed the calculated field with yyyy/MM/dd format.

Thanks for the help and have great day!

2 Likes