Conditional WHERE statement to show a certain Fiscal Week Number

Question

Looking for some guidance on how to best execute this code...

Ultimately, I want to insert this into the where clause so that the FSCL_YR_WK (i.e. 201804) will be driven based on the current date.

AND A.FSCL_YR_WK =  Case 
                    WHEN GETDATE()  between 2018-02-26 AND 2018-03-04 THEN 201804 
                    WHEN GETDATE()  between 2018-03-05 AND 2018-03-11 THEN 201805
                    WHEN GETDATE()  between 2018-03-12 AND 2018-03-08 THEN 201806  
                ELSE '' END

score 0 · Answer 1 · answered Feb 28 '18 at 21:42

ugly but works:

declare @d datetime
SET DATEFIRST 1--week starts on monday, default is sunday
set @d=convert(date,'2018-02-26')
select convert(char(4),datepart(year,@d))+RIGHT('0'+CAST(DATEPART( wk, @d)-5 AS VARCHAR(2)),2)

maybe put it in a function just to keep select clear

Conditional WHERE statement to show a certain Fiscal Week Number

1 Answers1