Trifacta Query for Custom SQL

Question

What I’m trying to solve is to write a Custom SQL in Trifacta like:

Select * from (Dataset) where Cast(businessdate as Date) == DateAdd(day, -1, YYYY-MM-DD)

Idea is to select the rows from a dataset where businessdate is yesterday’s date. But the Custom SQL is not accepting the syntax.

Can someone please guide?

apathetichell · Answer

@Aryasuta_Panda - this is for Trifacta/Designer Cloud - not Desktop/Designer. @GautamK - try in the Designer Cloud forum - my hunch is your underlying DB SQL function syntax is different so you'd need to adjust your functions to that. What's your underlying DB you are running against.

Aryasuta_Panda · Answer

Hi, first of all please check if the data present in the field businessdate is correct as per the cast you have used. Secondly there should be a filed name or a date vale (eg: '2024/01/01') in place of "YYYY-MM-DD". One more suggestion would be don't use in-db tools for the query. Use dynamic input instead.

Thanks

Screenshot 2024-02-08 115541.png