Failure to parse date/time

LeonN

Any help on this would be appreciated, I can't seem to get rid of this error and I'm not sure what I'm doing wrong or what I need to check.

I'm duplicating an ETL we currently use for a similar dataset, but this dataset is separated by day, rather than month.

MichelleH

@LeonN The %d specifier in the DATE_FORMAT function is expecting a leading zero in the day, so it is expecting 'Jan 01, 23' instead of 'Jan 1, 23'. You may need to manipulate the field to include a leading zero before the day before using the DATE_FORMAT function. Alternatively you could try using the FUZZY_PARSE_DATE function instead of DATE_FORMAT.

marcel_luthi

You should be able to use %e instead of %d for the day part to account for the day not being provided in the 2 digits format, and remember to use STR_TO_DATE and not the DATE_FORMAT function, as what you have is a string you want to turn into a date and not displaying a date in a specific string format. I just tested my previous suggestion and seems to be working for me:

STR_TO_DATE(`Year Day Month (text)`,'%b %e, %y')

I have doubts as in the original post you mentioned having a column called Year Month Date (text) based on your ETL screenshot, but in the table screenshot you're showing the dates to be column names and a column each. What is it that you're trying to achieve? What does your ideal output look like (I doubt you want to have a separate column per each date) and what does your current input look like?

MichelleH

@LeonN What do the dates in your data look like?

LeonN

Jan 1, 23; Jan 2, 23 etc. (see column names below)

marcel_luthi

Based on the error in red, it seems your date string includes a comma after the day number and this one is using a single digit version when below day 10, this could be the reason it fails. Try updating your date format to:

STR_TO_DATE(`Year Day Month (text)`,'%b %e, %y')

GrantSmith

You need to add a comma to your date format:

DATE_FORMAT(`MyDateColumn`, '%b %d, %y')

For others who come across this there's a great Domo KB article on date format string you can find here:

https://domo-support.domo.com/s/article/360043429953?language=en_US

LeonN

I did try adding a comma and I'm still getting the same error.

MarkSnodgrass

It also seems like you have some slashes before and after your date. I would try adding a REPLACE function to remove those and then convert to date.

REPLACE(fieldname,'/','')

LeonN

Thanks for the suggestions, but still not having any luck

MichelleH

@LeonN The %d specifier in the DATE_FORMAT function is expecting a leading zero in the day, so it is expecting 'Jan 01, 23' instead of 'Jan 1, 23'. You may need to manipulate the field to include a leading zero before the day before using the DATE_FORMAT function. Alternatively you could try using the FUZZY_PARSE_DATE function instead of DATE_FORMAT.

marcel_luthi

You should be able to use %e instead of %d for the day part to account for the day not being provided in the 2 digits format, and remember to use STR_TO_DATE and not the DATE_FORMAT function, as what you have is a string you want to turn into a date and not displaying a date in a specific string format. I just tested my previous suggestion and seems to be working for me:

STR_TO_DATE(`Year Day Month (text)`,'%b %e, %y')

I have doubts as in the original post you mentioned having a column called Year Month Date (text) based on your ETL screenshot, but in the table screenshot you're showing the dates to be column names and a column each. What is it that you're trying to achieve? What does your ideal output look like (I doubt you want to have a separate column per each date) and what does your current input look like?