How do you change the order date "2020-01-06T16:56:00" to the first day of the month?
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 heart 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 the heart icon**
**Did this solve your problem? Accept it as a solution!**0 -
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 the heart icon**
**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
- 10.6K All Categories
- APAC User Group
- 12 Welcome
- 36 Domo News
- 9.6K Using Domo
- 1.9K Dataflows
- 2.4K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 338 Workbench
- 250 Domo Best Practices
- 11 Domo Certification
- 460 Domo Developer
- 47 Domo Everywhere
- 100 Apps
- 703 New to Domo
- 84 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 22 お知らせ
- 62 Kowaza
- 295 仲間に相談
- 649 ひらめき共有