How do you change the order date "2020-01-06T16:56:00" to the first day of the month?

gbennett

How do you change the order date "2020-01-06T16:56:00" to the first day of the month? Via transform.

jaeW_at_Onyx

@gbennett ,the problem is literally in the error returned, invalid column name.

 

SELECT
DATE(`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 ...'

GrantSmith

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. 

gbennett

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.

 

 

GrantSmith

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`

 

gbennett

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

 

jaeW_at_Onyx

@gbennett ,the problem is literally in the error returned, invalid column name.

 

SELECT
DATE(`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 ...'