Posted By

michanne on 03/25/11


Tagged

transact-sql


Versions (?)

List of Weeks


 / Published in: SQL
 

  1. declare @numofweeks int = 0, @Date date = getdate(), @FirstDayOfWeek tinyint = 6
  2. SET DATEFIRST @FirstDayOfWeek;
  3.  
  4. Declare @EndDate date
  5. Declare @BeginDate date
  6. IF @numofweeks < 0
  7. begin
  8. SET @BeginDate = dateadd(week,@numofweeks,dateadd(day,-(DATEPART(WEEKDAY,@Date)-1),@Date))
  9. end
  10. else
  11. begin
  12. SET @BeginDate = dateadd(day,-(DATEPART(WEEKDAY,@Date)-1),@Date)
  13. end;
  14.  
  15. WITH wk AS (
  16. SELECT CAST (7 AS int) AS n, @BeginDate AS BeginDate,DATEADD(day,6,@BeginDate) AS EndDate
  17. UNION ALL
  18. SELECT N+7, DATEADD(DAY,(1),EndDate),DATEADD(DAY,(7),EndDate)
  19. FROM wk
  20. WHERE n < 7*ABS(@numofweeks)
  21. )
  22. SELECT ROW_NUMBER() over(ORDER BY BeginDate) AS Week,BeginDate,datename(weekday,BeginDate) AS BeginDay,EndDate,DATENAME(weekday,EndDate) AS EndDay
  23. FROM wk
  24. ORDER BY BeginDate;

Report this snippet  

You need to login to post a comment.