CODEWITHSUNDEEP
sqldatesnowflake-cloud-data-platformsnowflake-task
Lawrence
Lawrence

Reputation: 11

End of week of current week in Snowflakes

I have a date column called Close_Date.

How do i get the Close_date to only give me date for end of the current week?

Thanks

Upvotes: 1

Views: 3199

Answers (2)

Sriram B
Sriram B

Reputation: 1

You can use the following SQL in your tables DDL and It will give you the Week ending date as Saturday of the Current week. For this you don't need to change session parameters every time.

SELECT TO_DATE('2021-01-01') AS DATE,
IFF(DAYOFWEEK(DATE) = 0,DATEADD(DAY,6,DATE),DATEADD(DAY,-1,LAST_DAY(DATE,'week'))) AS Week_Ending_Dt

Change DATE as per your need.

Upvotes: 0

Mike Gohl
Mike Gohl

Reputation: 737

You can use the Last_Day function. There is a parameter WEEK_START that affects which day is the last day of the week. Here is the link below:

https://docs.snowflake.net/manuals/sql-reference/functions/last_day.html 

ALTER SESSION SET WEEK_START = 6;
SELECT LAST_DAY(current_date, 'week');

Upvotes: 2

Related Questions