ParseDate returns nulls

Q&A
, ,
1

I have a date field that has string type in format ‘yy/mm’ like ‘24/12’ , ‘24-11’ and I use the below calculated field but it returns only nulls.

image
image411×229 4.96 KB

The date field comes from uploading an excel file. Also when trying to change the type in the dataset from string to date the dataset returns me null rows

image
image1058×555 22.6 KB

image
image1544×146 9.37 KB

so why when changing the data type to date in the dataset the whole dataset breaks and why the parseDate doesn’t work?

2

Hello @pantelis Hope this message finds you well!

My suggestion to dealing with date fields that are stored as strings, is to ensure that the format is correctly specified when converting them to date types. If your date strings are in the format 'yy/mm' or 'yy-mm', you can use calculated fields to parse these strings into date objects.

Here’s an example of how you might define a calculated field in Quick Sight to parse a date string:

ifelse(
    strpos({date_field}, '/') > 0,
    parseDate({date_field}, 'yy/MM'),
    parseDate({date_field}, 'yy-MM')
)

This checks if the date string contains a ‘/’ and uses the appropriate format to parse the date. If the parsing function returns nulls, ensure that all date strings conform to the expected format and handle any parsing errors or invalid formats appropriately.

Please, tell me if it helps you!