Hi all, I am trying to use parseDate to convert a string into a date however, whenever there are exceptions on the date format, parseDate does not work at all. Is there any way to circumvent this and print something like “date not available” instead of having parseDate breaking everything?
I could go one by one and edit all the exceptions to go to the same format so that parseDate could be used. However, a lot of new data is coming in with different formats and I need a way for my visuals not to break every time this happens.
Hi @mateoleon210
I wonder since it sounds like you have lots of different data with different formats whether you could connect to it using Amazon Athena? This would allow you to make use of some other functions that are available, in particular the TRY function, where you could create query that tests multiple date formats and returns the format you want.
Since parseDate returns a date field you would not be able to use ‘date not available’ but you could return null instead.
Hi Steph, tysm for your response. I am trying to figure this out on quicksight if possible. I wrote a function that checks every item in a string before I run the string through parseDate so that I know if parseDate is going to work or break everything. I did this through an ifelse statement.
ifelse(conditional, parseDate(dates_to_be_parsed, ‘yyyy-MM-dd’), “no date available”)
However, I can’t include a function as the output of my ifelse statement. This means that I would have to do:
ifelse(conditional, toString(parseDate(dates_to_be_parsed, ‘yyyy-MM-dd’)), “no date available”)
. Which would defeat the purpose of this whole formula.
So my follow-up question is: Is there any way to have a conditional formula that would allow me to run parseDate only if the condition is met? without converting it back to a string.
