Question on Domo SQL syntax

Aag2023

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!

GrantSmith

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.

MichelleH

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

GrantSmith

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.

Aag2023

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

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

Aag2023

Sure @MichelleH

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

Product Key (Floating decimal type) -

ProductSale_TimeStamp (Time stamp type)-

ColemenWilson

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

MichelleH

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

Aag2023

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