How to add/subtract weeks to the third day of the week?

Question

I'm trying to figure out how to translate this line of PROC SQL code into Snowflake SQL but haven't found a way yet.

%LET last_post_dt = %SYSFUNC(INTNX(WEEK.3,%SYSFUNC(TODAY(),),-2,B),DATE9.);

Basically, its subtracting 2 weeks from the third day of the current week (monday = 1). I've tried altering the session by using WEEK_START and other functions such as DATEADD, but haven't been able to solve this.

Thanks in advance!

score 0 · Accepted Answer · answered Aug 26 '22 at 22:29

You can use date_trun() to get whatever today is to a Monday. Then add 2 for 3rd day of the week, and then subtract 2 weeks from that.

SELECT DATE_TRUNC('week',current_date()) as week_start,
       DATEADD('day',2,week_start) as week_3rd_day,
       DATEADD('week',-2,week_3rd_day) as weeks_ago,
       DATEADD('week',-2,DATEADD('day',2,DATE_TRUNC('week',current_date()))) as all_in_one_line_date

How to add/subtract weeks to the third day of the week?

1 Answers1