String to Date

nj-John-mirc
nj-John-mirc Member
in Magic ETL

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 Member
    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 Coach
    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')

     

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • MichelleH
    MichelleH Coach

    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 Coach

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    can you be specific about what's not working?

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • nj-John-mirc
    nj-John-mirc Member

    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 Coach

    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
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • nj-John-mirc
    nj-John-mirc Member
    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!

  • nj-John-mirc
    nj-John-mirc Member

    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 Coach

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • nj-John-mirc
    nj-John-mirc Member

    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 Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • nj-John-mirc
    nj-John-mirc Member

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

  • MichelleH
    MichelleH Coach

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

  • GrantSmith
    GrantSmith Coach
    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')

     

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • snguyen
    snguyen Member

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

Categories