String to Date

nj-John-mirc

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

nj-John-mirc

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!

GrantSmith

@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')

 

MichelleH

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. 

GrantSmith

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)?

jaeW_at_Onyx

can you be specific about what's not working?

nj-John-mirc

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

 

 

 

 

 

GrantSmith

Try using %-d instead of %d. 

 

%d	Day of the month as a zero-padded decimal number.	03
%-d	Day of the month as a decimal number. (Platform specific)	3

nj-John-mirc

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

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?

GrantSmith

@nj-John-mirc 

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.

nj-John-mirc

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/"

GrantSmith

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.

nj-John-mirc

I'm using it in a FORMULA tile within ETL.  the / symbol is part of the error output

MichelleH

If you're using a MagicETL, have you tried using the Set Column Type tile to change it from text to a date?

GrantSmith

@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')

 

snguyen

I'm having the same problem with the / symbol part of the error output. Did you find the solution for this one?