How do I alter Date (in Text format : 01.09.2023) to Date format?
Best Answers
-
You can use the Alter Column tile to convert your string to a date type field. You'll need to specify the date format as %d.%m.%Y.
Alternatively you can use a formula tile and the STR_TO_DATE function to convert it to a date:
STR_TO_DATE(`date_field`, '%d.%m.%Y')
Here's more information on date formatting characters for reference:
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Based on the error message, it looks as though one of your rows contains a value of LAST_RUN and not any form of a date. You can try and handle this two different ways. In your formula tile, you can try wrapping a TRY_CAST function around your STR_TO_DATE function like this:
TRY_CAST(STR_TO_DATE(
Date
, '%Y-%m-%d') as DATE)This will attempt to convert it to a date field and if it can't it will just make the value null.
In the alter tile, click on the settings cog next to the cast data type and select Null in the Bad Values dropdown list. This will do the same thing as above, as it will make the value null if it can't convert it to a date for you.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Answers
-
You can use the Alter Column tile to convert your string to a date type field. You'll need to specify the date format as %d.%m.%Y.
Alternatively you can use a formula tile and the STR_TO_DATE function to convert it to a date:
STR_TO_DATE(`date_field`, '%d.%m.%Y')
Here's more information on date formatting characters for reference:
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Thank you so much @GrantSmith ! The first solution proposed by you worked!
0 -
Hi @GrantSmith ,
Sorry I have another question : The dataset I have contains date in this format :
I have used RIGHT(
Date
, 10) to get :and I've tried the suggestions by you ('%Y-%m-%d') on the Alter tile and Formula tile, which both doesnt work. It gave me this error message :
Appreciate your help!
0 -
Based on the error message, it looks as though one of your rows contains a value of LAST_RUN and not any form of a date. You can try and handle this two different ways. In your formula tile, you can try wrapping a TRY_CAST function around your STR_TO_DATE function like this:
TRY_CAST(STR_TO_DATE(
Date
, '%Y-%m-%d') as DATE)This will attempt to convert it to a date field and if it can't it will just make the value null.
In the alter tile, click on the settings cog next to the cast data type and select Null in the Bad Values dropdown list. This will do the same thing as above, as it will make the value null if it can't convert it to a date for you.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 310 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 113 SQL DataFlows
- 650 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 791 Beast Mode
- 78 App Studio
- 43 Variables
- 753 Automate
- 187 Apps
- 478 APIs & Domo Developer
- 71 Workflows
- 17 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 407 Distribute
- 118 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 141 Manage
- 137 Governance & Security
- 8 Domo Community Gallery
- 47 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive