How can i get last 13 weeks in T SQL excluding current week?-CodePudding

I am working on a project and need to pull last 13 weeks of data. I have tried datediff in filter but it is pulling extra weeks. I have already set datefirst to 1 but still not getting desired result.

WHERE clause is

DATEDIFF(WEEK,dt.date_key,getdate())<=13

CodePudding user response：

Try this code:

SELECT date_key FROM tbl WHERE date_key  BETWEEN DATEADD(week, -13,GETDATE()) AND  DATEADD(week, -1,GETDATE())

CodePudding user response：

I have managed to get the answer. Put below in where clause and it has worked as expected.

d.date_key is my date column.

d.date_key >= DATEADD(dd,  0, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, GETDATE())) - 13, 0)) and d.date_key <= DATEADD(dd,  6, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, GETDATE())) - 1, 0))