How do you change the order date "2020-01-06T16:56:00" to the first day of the month? Via transform.
@gbennett ,the problem is literally in the error returned, invalid column name.
SELECTDATE(`order_date`) - INTERVAL (DAYOFMONTH(`order_date`)) DAY + INTERVAL 1 DAY
AS 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 ...'
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.
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.
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`
Hey @GrantSmith Grant,
This my statement:
SELECTDATE(`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
