CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Alain
Alain

Reputation: 401

Snowflake SQL How to get only values from the last full week

I'm trying to build a query that would get me only the results with a created date from the last full week. So, for example, if today is Monday 2021-06-28, I only want the results from Monday 2021-06-21 to Sunday 2021-06-27.

I tried with this, but this is the last 7 days, without considering week end or start.

WHERE (CREATED_AT::DATE BETWEEN (CURRENT_DATE::DATE - INTERVAL '1 WEEK') AND CURRENT_DATE::DATE)

I also tried working with this function:

last_day(CREATED_AT::DATE, 'week') as "LAST_DAY_OF_WEEK"

and then trying to substract 7 days, but I think my use of these functions is incorrect.

Upvotes: 3

Views: 8848

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Use date_trunc():

WHERE CREATED_AT >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '7 DAY' AND
      CREATED_AT < DATE_TRUNC('week', CURRENT_DATE)

Upvotes: 4

Lukasz Szozda
Lukasz Szozda

Reputation: 176074

You could use:

WHERE CREATED_AT::DATE BETWEEN DATEADD(wk, DATEDIFF(wk, '1900-01-01'::DATE, CURRENT_DATE())-1,'1900-01-01'::DATE)
  AND DATEADD(d, 6, DATEADD(wk, DATEDIFF(wk, '1900-01-01'::DATE, CURRENT_DATE())-1,'1900-01-01'::DATE))

The weeks are calculated:

SELECT DATEADD(wk, 
              DATEDIFF(wk, '1900-01-01'::DATE, CURRENT_DATE())-1,
              '1900-01-01'::DATE) AS prev_week,
       DATEADD(d, 6, prev_week) AS current_week
/*
PREV_WEEK   CURRENT_WEEK
2021-06-21  2021-06-27
*/

Upvotes: 1

Related Questions