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

gbennett
gbennett ⚪️
in Dataflows

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

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    Accepted Answer

    @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 ...'

Answers

  • GrantSmith
    GrantSmith Indiana 🔴

    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
    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
    GrantSmith Indiana 🔴

    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
    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

     

Categories