number weeks from April to September for every year

Anonymous
2020-09-18T20:11:15.52+00:00

I want to number weeks from April to September for every year, such that week 1 starts 1st of April and ends the following Saturday and week 2 starts first Sunday of April and ends the following Saturday and so on.... till the end of September. How do I do that using TSQL.

Thanks in advance.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,524 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 99,546 Reputation points MVP
    2020-09-19T08:53:36.09+00:00

    You already have a calendar table, great!

    The you don't the table of numbers I talked about - the calendar table is an extension of that concept.

    I think this should work:

    SET DATEFIRST 7 
    UPDATE CalendarTable
    SET    NewWeekCol = datepart(week, CalendarDate) - 
                        datepart(week, datename(YEAR, CalendarDate) + '0401') + 1
    WHERE  month(CalendarDate) BETWEEN 4 AND 9
    

    But note that since I don't your table, I cannot test. But you could tweak it on your own.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 99,546 Reputation points MVP
    2020-09-18T22:01:18.387+00:00

    To that end you would need a table of numbers. This is a concept that I discuss in this (relatively) short article on my web site: http://www.sommarskog.se/Short%20Stories/table-of-numbers.html

    I don't give any query, since you did not give any table to work with,


  2. Anonymous
    2020-09-20T03:20:04.343+00:00

    Thank You ErlandSommarskog .
    That works like a charm.

    0 comments No comments