Unable to convert string data type to date data type

Question & Answer
, , ,
1

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

2

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:

2023-02-18_23-21-55
2023-02-18_23-21-551204×916 57.1 KB

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

Getting these two diffrent error:

2023-02-18_23-23-08
2023-02-18_23-23-081212×998 73.4 KB

2023-02-18_23-24-12
2023-02-18_23-24-121174×946 61.2 KB

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

3

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
4

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

5

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
6

I don’t understand what the solution is and am facing a similar issue. I have a date field that is a string, ex 2024-02. How do I most effectively convert this into an actual date field that uses the first of the month as the day? So in our example the conversion would be 02-01-2024.

I used parse date to extract the 2024 into a year field and the 02 into a month field but didn’t know how to combine the information into a date with the first of the month as the day.

Is it best to try to use a converter in the data set? Or is it better to use a calculated field to create a converted date? I’m thinking the latter because then I would have two different date options when creating visuals. Your help would be greatly appreciated!

7

Hi @lsaocr27
I would recommend playing with strings and creating the full date as a string and then converting the string into a date data type.
For example, if all your data field called “Date” looks like “2024-02”, you can use concat to add the extra information and only then parse. It would look something like this:
Concat({Date},“-01”)
And then parse after that.

1 Like
9

This is what I did: my original event_date was a sting in yyyy-MM-dd format, 2024-12-31 for example.
I created a new calculated field, parsed_date with value parseDate(event_date, 'yyyy-MM-dd')

10

For me it works with: yyyy-MM-dd’T’HH:mm:ss.SSS’Z
I think you might have an issue with using ’ instead of ’

1 Like