Convert String To Date Format
Hello,
I'm working in Magic ETL.
I have two problems
1 I have a Hire Date field with a string data type that has the string in 2 different formats
01/01/19 or 01/01/2019 How can I convert the values to one format?
2 How can I convert the standardized string to a date. I tried to use str to date but it failed
each time.
Best Answer
-
Hi @micheleb
You'll need to put everything in the same format and then convert it to a date. You can convert the date strings using a REGEX_REPLACE function in a formula tile:
REGEXP_REPLACE(`Date`, '^(\d{2})\/(\d{2})\/(\d{2})$', '$1/$2/20$3')
This will check for a 2 digits / 2 digits / 2 digits format and then put a 20 in the last field to default to this millennium so that you have a 4 digit year. If it doesn't match the format it will still just return the string so your MM/DD/YYYY format strings will be untouched.
Once they're in the same format you can then use an Alter Columns tile to change the type of the date column from string to Date.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
Hi @micheleb
You'll need to put everything in the same format and then convert it to a date. You can convert the date strings using a REGEX_REPLACE function in a formula tile:
REGEXP_REPLACE(`Date`, '^(\d{2})\/(\d{2})\/(\d{2})$', '$1/$2/20$3')
This will check for a 2 digits / 2 digits / 2 digits format and then put a 20 in the last field to default to this millennium so that you have a 4 digit year. If it doesn't match the format it will still just return the string so your MM/DD/YYYY format strings will be untouched.
Once they're in the same format you can then use an Alter Columns tile to change the type of the date column from string to Date.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Do you have non-date data in this field? If not, when you are in the ETL you can select that input dataset and on the configuration tab, change the data type from text to Date (assuming that it is currently text). Domo should auto-format your dates to normalize this data for you. If this doesn't work for you, let me know and there are some beast mode things you can do, but this is the easiest solution.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Thanks so much ! The Regex formula worked perfectly. I still had some folks who were hired in the 1900's but I'm thinking I could use a an If then statement like If Hire Date year is 2050 or greater to switch it to 1900's
0 -
@micheleb just out of curiosity, did you try a beast mode like this:
DATE(yourdatefield)
I don't have a sample dataset to try it on, but wondered if that throws an error, or if it standardizes the data for you.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
The problem is the data in the column is not uniform so trying to use beast mode or changing data types won't work without changing the strings to consistent standard format won't work for a beast mode or simple data type change. Some strings are formatted 00/00/00 some strings are formatted 00/00/0000 some are null.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive