Date Format change

I have a few columns which use the date format 01/Jan/2016, I need to convert these to 'Date' to use as date ranges in metrics.
I have tried using the 'replace' text card in an ETL to change the forward slashes and months to numbers. This works on some rows but not all (trying to figure out why). Then I change the data type to 'Date'. I don't have any errors but there is a lot of data missing.
Is there an easier way for me to convert these columns into date format?
Many thanks
Best Answer
-
@louiswatson you can also try using the input dataset data handling section. Click on your input in an ETL and then select data Handling, you can choose data type Date and then click the settings icon for more details.
0
Answers
-
@louiswatson You can use the STR_TO_DATE() function to convert those values to date like this:
STR_TO_DATE(`DateField`,'%d/%b/%Y')
The second argument of the function uses these date format specifiers to specify what format the existing field uses.
2 -
@louiswatson you can also try using the input dataset data handling section. Click on your input in an ETL and then select data Handling, you can choose data type Date and then click the settings icon for more details.
0 -
Sometimes figuring out the date syntax can be annoying - I usually ask chatGPT like "I have text like "12/31/23" how do I make it a date in MySQL" and it will spit out the proper syntax… most of the time.
0
Categories
- All Categories
- 1.1K Product Ideas
- 1.1K Ideas Exchange
- 1.2K Connect
- 970 Connectors
- 259 Workbench
- Cloud Amplifier
- 1 Federated
- 2.4K Transform
- 76 SQL DataFlows
- 502 Datasets
- 1.8K Magic ETL
- 2.7K Visualize
- 2.2K Charting
- 379 Beast Mode
- 21 Variables
- 487 Automate
- 104 Apps
- 379 APIs & Domo Developer
- 6 Workflows
- 22 Predict
- 6 Jupyter Workspaces
- 16 R & Python Tiles
- 319 Distribute
- 67 Domo Everywhere
- 252 Scheduled Reports
- 59 Manage
- 59 Governance & Security
- 1 Product Release Questions
- 5K Community Forums
- 37 Getting Started
- 23 Community Member Introductions
- 64 Community Announcements
- 4.8K Archive