Q: How do I get the week number by starting as 1 from a specific date of the year in SQL Server? Been trying out quite a lot of samples posted in the forum but to no avail.  The week number given is per calendar year.  How do I go about getting week number like this:
Start Date: 1st Mar 2021 (Week number starts at 1 based on input start date)
Subsequent Date     | Week Number
--------------------|------------
1 - 7 Mar 2021      | 1
8 - 14 Mar 2021     | 2
15 - 21 Mar 2021    | 3
21 - 28 Mar 2021    | 4
29 Mar - 2 Apr 2021 | 5
.
.
.
.

Week start on a Monday.  Week Number is running up till end of fiscal year, i.e. end of Feb.  It is reset to 1 at every March.

A: You can subtract the dates and divide by 7.  Naively, this would be:
1 + datediff( day, datefromparts(year(datecol), 3, 1), datecol ) / 7

This doesn't work for January and February.  So we can use a little trick of subtracting 2 months from the date for the year:
1 + (datediff( day,
               datefromparts(year(dateadd(month, -2, datecol)), 3, 1),
               datecol
             ) / 7)

