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)

​x
 
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"

 
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.

Answer

Use date_trunc():

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

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

Advertisement

Answer