Hi
I'm trying to convert a string to a date where the string shows "day Month", ie "30 Aug" or "23 Jul" (double quotes not included in the string). The string to date statement doesn't seem to work. Any ideas?
Thx
John
Yes, that seems to work for the single digits but not for the double digits, so it's just a matter of combining the two formulas - %d and %-d.
Thank you!
@nj-John-mirc
Interestingly enough the formula STR_TO_DATE is using a different formatting character set than the Beast Mode does. Instead of %-b you want %e. For a reference on the formatting characters you can look at https://www.mysqltutorial.org/mysql-date_format/
STR_TO_DATE(`Date As String`, '%e %b')
The STR_TO_DATE() function should work if you match the format of your date column using the Date Format Specifiers: https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Transforming_Data_Using_Beast_Mode/03Date_Format_Specifier_Characters_in_Beast_Mode
Based on the example you gave, this code should convert the dates:
STR_TO_DATE(`DateCol`,'%d %b')
Note that this will return the year as 0001 if there is no year in your date column. You can adjust this in the date format settings of the field so that it does not show a year, if this is the case.
Hi @nj-John-mirc
Have you tried concatenating the appropriate year onto your string and then using STR_TO_DATE?
STR_TO_DATE(CONCAT(`Date`, ' ', YEAR(CURRENT_DATE)), '%d %b %Y')
How is the STR_TO_DATE function not working?
Does using the format string '%d %b' work in STR_TO_DATE (without concatenating the year)?
can you be specific about what's not working?
OK, I've narrowed it down to the number of characters representing the day. Both formulas work until it hits the single digit representing the day, so it works for 30 Aug but it crashes at 9 Aug
Try using %-d instead of %d.
Apologies, I'm new to formulas and statements. How can I combine the two formulas so the single digit and double digit appear in the column?
How does it not work for the double digits? What's the formula you're currently using? %-b should handle single and double digit day numbers.
Seems each formula works separately...
STR_TO_DATE(`After -`,'%-d %b') Error - "Failed to parse date/time: error at near character 0 in string /30 Aug/"
STR_TO_DATE(`After -`,'%d %b') Error - "Failed to parse date/time:error at near character 0 in string /9 Aug/"
Are you doing this in a beast mode or in a query on your database bringing the data in (if so which DB type)?
Do your dates actually include / or is that just from the error output text?
Utilizing STR_TO_DATE in a beast mode seems to work just fine for me.
I'm using it in a FORMULA tile within ETL. the / symbol is part of the error output
If you're using a MagicETL, have you tried using the Set Column Type tile to change it from text to a date?
I'm having the same problem with the / symbol part of the error output. Did you find the solution for this one?
try this calculation
STR_TO_DATE(simple_timestamp, '%Y-%m-%d')
simple_timestamp
