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

gbennett
gbennett Member
in Magic ETL

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

    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"

Answers

  • GrantSmith
    GrantSmith Coach

    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!**
  • gbennett
    gbennett Member

    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 Coach

    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!**
  • gbennett
    gbennett Member

    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
    jaeW_at_Onyx Coach
    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 ...'

    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"

Categories