How do you change the order date "2020-01-06T16:56:00" to the first day of the month?
How do you change the order date "2020-01-06T16:56:00" to the first day of the month? Via transform.
Best Answer
-
@gbennett ,the problem is literally in the error returned, invalid column name.
SELECT
DATE(`order_date`) - INTERVAL (DAYOFMONTH(`order_date`)) DAY + INTERVAL 1 DAYAS giveMeAName
,SUM(`order_value`)
AS `Punchout Revenue`
FROM
TABLE
If you don't name your column (the AS clause), Domo will try to fill it with the function you wrote, hence the error 'invalid name ...'
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"1
Answers
-
Hi @gbennett
If you have the ETL 2.0 beta you can utilize a formula tile and write a formula like (It'd be the same if you want to do it as a beast mode):
DATE(`Date`) - INTERVAL (DAYOFMONTH(`Date`)) DAY + INTERVAL 1 DAY
Essentially you're subtracting the day of the month from your date and adding 1 day to get the first of the month.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Hi @GrantSmith,
Thanks for your help. I am doing this in a SQL Transform creating a table.I received this message on execution.
I received a message that "The database reported a syntax error: Incorrect column name 'DATE(`order_date`) - INTERVAL (DAYOFMONTH(`order_date`)) DAY + INTERVAL 1 DAY'"
Any advice would be great.
0 -
Hi @gbennett
You're need to make sure you're selecting an SQL Table transform and select the columns you want from your table along with that code.
SELECT `col1`, `col2`, DATE(`order_date`) - INTERVAL (DAYOFMONTH(`order_date`)) DAY + INTERVAL 1 DAY from `my_table`
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Hey @GrantSmith Grant,
This my statement:
SELECT
DATE(`order_date`) - INTERVAL (DAYOFMONTH(`order_date`)) DAY + INTERVAL 1 DAY
,SUM(`order_value`) AS `Punchout Revenue`
FROM `punchout_all`Receiving this message:
The database reported a syntax error: Incorrect column name 'DATE(`order_date`) - INTERVAL (DAYOFMONTH(`order_date`)) DAY + INTERVAL 1 DAY'
The field type is timestamp vs date, i wonder if i need to change it to timestamp prior to being able to use the date function?
-gordon
0
Categories
- 7.3K All Categories
- 13 Getting Started in the Community
- 134 Beastmode & Analytics
- 1.8K Data Platform & Data Science
- 52 Domo Everywhere
- 2K Charting
- 994 Ideas Exchange
- 894 Connectors
- 236 Workbench
- 342 APIs
- 76 Apps
- 18 Governance & Productivity
- 233 Use Cases & Best Practices
- 49 News
- 473 Onboarding
- 570 日本支部