How can I translate this legacy SQL to standard SQL in BigQuery?

I need to translate this to BigQuery. Can anyone help? Thanks

IF DATEDIFF('day',DATEADD('day',7,snapshot_date),TODAY(),'monday')>=1
THEN 1
END

​x
 
IF DATEDIFF('day',DATEADD('day',7,snapshot_date),TODAY(),'monday')>=1THEN 1END​

This is the error I’m getting.

The Google BigQuery Standard SQL database encountered an error while running this query. Query execution failed: – Syntax error: Expected “(” but got identifier “DATEDIFF” at [2:25]

Answer

Try this (docs):

IF(DATE_DIFF(DATE_ADD(snapshot_date, INTERVAL 7 DAY), CURRENT_DATE(), WEEK(MONDAY)) >= 1, 1, NULL)

 
IF(DATE_DIFF(DATE_ADD(snapshot_date, INTERVAL 7 DAY), CURRENT_DATE(), WEEK(MONDAY)) >= 1, 1, NULL)​

Advertisement

Answer