Migrate to Standard SQL: choose the closest sunday from current date

1
votes

I need to migrate from legacy to standard SQL this query:

SELECT MAX(FECHA)
FROM(
SELECT FECHA, DAYOFWEEK(FECHA) AS DIA
FROM(
SELECT DATE(DATE_ADD(TIMESTAMP("2017-05-29"), pos - 1, "DAY")) AS FECHA
FROM (
     SELECT ROW_NUMBER() OVER() AS pos, *
     FROM (
       FLATTEN((
         SELECT SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP(CURRENT_DATE()), 
         TIMESTAMP("2017-05-29")), '.'),'') AS h
         FROM (SELECT NULL)),h
)))
))
WHERE DIA=1

The query must return the previous closest sunday date from current date.

When I run this in standard SQL I get

Syntax error: Expected keyword JOIN but got ")" at [12:2] (after FROM (SELECT NULL)),h

2
sqlgoogle-bigquerybigquery-standard-sql
What have you tried? What error do you get when you run this? Have you read this? cloud.google.com/bigquery/docs/reference/standard-sql/… - Nick.McDermaid
This query works into Legacy SQL. No messages error appears. I need to migrate it into standard SQL, and I don't know how to deal with this language - Mario M.
So when you run it in Standard SQL What error message do you get? - Nick.McDermaid
Syntax error: Expected keyword JOIN but got ")" at [12:2] (after FROM (SELECT NULL)),h - Mario M.
I will add this to your question. In future, please do this in the first instance - Nick.McDermaid

2 Answers

2
votes

The query must return the previous closest sunday date from current date. 

#standardSQL
SELECT 
  DATE_SUB(CURRENT_DATE(), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) - 1 DAY)

You can replace CURRENT_DATE() with any date and it will return previous closest Sunday

2
votes

You can use DATE_TRUNC with the WEEK part to truncate to the most recent Sunday. For example:

#standardSQL
WITH Input AS (
  SELECT date
  FROM UNNEST([
    DATE '2017-06-26',
    DATE '2017-06-24',
    DATE '2017-05-04']) AS date
)
SELECT
  date,
  FORMAT_DATE('%A', date) AS dayofweek,
  DATE_TRUNC(date, WEEK) AS previous_sunday
FROM Input;

This returns:

+------------+-----------+-----------------+
|    date    | dayofweek | previous_sunday |
+------------+-----------+-----------------+
| 2017-06-24 | Saturday  |      2017-06-18 |
| 2017-05-04 | Thursday  |      2017-04-30 |
| 2017-06-26 | Monday    |      2017-06-25 |
+------------+-----------+-----------------+