Question on Domo SQL syntax

A2024
A2024 Member
in Magic ETL

Hello all,

I am trying to do Magic Transform using SQL option. I have embedded a SQL query but it is erring as it is not compatible with Domo syntax. Appreciate your help in this regard.

DATE_PART('WEEK',CAST(TO_CHAR(TO_DATE(TO_CHAR(PRODUCT_KEY),'YYYYMMDD'),'YYYY-MM-DD') AS DATE)) AS "SaleWeek",

DATE_PART('YEAR',DATE(ProductSale_Timestamp))

I am getting syntax error while running my SQL query in Domo. I believe it is because of the above conditions as Domo is not compatible with DATE_PART. Can someone let me know an equivalent condition I can use in Domo SQL?

Thank you!

Tagged:

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Are you using a Magic ETL transformation with a formula tile or a MySQL transformation?

    Is your PRODUCT_KEY field a date field or a text field or a numerical field?

    Why are you casting it to a string, then to a date, then to a string and finally back to a date again?

    Assuming you're using a formula tile in Magic ETL:

    If PRODUCT_KEY is a date then you can simplify it with:

    WEEK(`PRODUCT_KEY`)

    If it's a string:

    WEEK(STR_TO_DATE(`PRODUCT_KEY`, '%Y%m%d'))

    If it's a number:

    WEEK(STR_TO_DATE(STRING(`PRODUCT_KEY`), '%Y%m%d'))

    Your other formula becomes:

    YEAR(`ProductSale_Timestamp`)

    Domo utilizes the MySQL format strings for dates and most of the function names / types.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MichelleH
    MichelleH Coach
    Answer ✓

    @Aag2023 For the product key, you can transform this into a date using the STR_TO_DATE() function:

    WEEK(STR_TO_DATE(`PRODUCT_KEY`,'%Y%m%d'))

    Since `ProductSale_Timestamp` is already in a datetime format, @GrantSmith's formula should already work for that.

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Are you using a Magic ETL transformation with a formula tile or a MySQL transformation?

    Is your PRODUCT_KEY field a date field or a text field or a numerical field?

    Why are you casting it to a string, then to a date, then to a string and finally back to a date again?

    Assuming you're using a formula tile in Magic ETL:

    If PRODUCT_KEY is a date then you can simplify it with:

    WEEK(`PRODUCT_KEY`)

    If it's a string:

    WEEK(STR_TO_DATE(`PRODUCT_KEY`, '%Y%m%d'))

    If it's a number:

    WEEK(STR_TO_DATE(STRING(`PRODUCT_KEY`), '%Y%m%d'))

    Your other formula becomes:

    YEAR(`ProductSale_Timestamp`)

    Domo utilizes the MySQL format strings for dates and most of the function names / types.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • A2024
    A2024 Member

    Thank you @GrantSmith for your response. I am using MySQL transformation. The data type for the produtkey field is Floating decimal in Domo. I tried above formats and it did not work. Thanks for sending the link. I will check the same.

  • MichelleH
    MichelleH Coach

    @Aag2023 Can you please share some example values of the `PRODUCT_KEY` and `ProductSale_Timestamp` fields?

  • A2024
    A2024 Member

    Sure @MichelleH

    In DOMO, This is how it looks like when I look at the data section.

    Product Key (Floating decimal type) -

    image.png

    ProductSale_TimeStamp (Time stamp type)-

    image.png

  • ColemenWilson
    ColemenWilson Coach

    Grants solution for the second date is correct:

    1. YEAR(`ProductSale_Timestamp`)

    Is that still not working for you?

    For the first part try this:

    YEAR(FROM_UNIXTIME(`PRODUCT_KEY`))

    If I solved your problem, please select "yes" above

  • MichelleH
    MichelleH Coach
    Answer ✓

    @Aag2023 For the product key, you can transform this into a date using the STR_TO_DATE() function:

    WEEK(STR_TO_DATE(`PRODUCT_KEY`,'%Y%m%d'))

    Since `ProductSale_Timestamp` is already in a datetime format, @GrantSmith's formula should already work for that.

  • A2024
    A2024 Member

    Thank you @MichelleH and @GrantSmith! This worked!!!

Categories