String to Date

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

Best Answers

  • nj-John-mirc
    nj-John-mirc ⚪️
    Accepted Answer

    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
    GrantSmith Indiana 🔴
    Accepted Answer

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

     

Answers

  • 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. 

    Date Format.PNG

  • GrantSmith
    GrantSmith Indiana 🔴

    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
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    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

     

     

     

     

     

  • GrantSmith
    GrantSmith Indiana 🔴

    Try using %-d instead of %d. 

     

    %dDay of the month as a zero-padded decimal number.03
    %-dDay of the month as a decimal number. (Platform specific)3
  • 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
    GrantSmith Indiana 🔴

    @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.

  • 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
    GrantSmith Indiana 🔴

    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?