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
-
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!
0 -
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!**0
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.
1 -
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!**0 -
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"0 -
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
0 -
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 **Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 -
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!
0 -
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?
0 -
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!**0 -
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/"
0 -
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!**0 -
I'm using it in a FORMULA tile within ETL. the / symbol is part of the error output
0 -
If you're using a MagicETL, have you tried using the Set Column Type tile to change it from text to a date?
0 -
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!**0 -
I'm having the same problem with the / symbol part of the error output. Did you find the solution for this one?
0 -
try this calculation
STR_TO_DATE(
simple_timestamp
, '%Y-%m-%d')0 -
✅Did this solve your problem? Accept it as a solution!
❤️Did you love this answer? Mark it as "Awesome"!
👍Do you agree with this process? Click "Agree"!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 56 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive