Firstly, sorry for the long column, I had to post it all so you can better understand what I'm struggling with.
I have a calendar with TheDate and the ISOWeek.
What I'm trying to do is to create another column that represents the month of that date. The numbering will start with 1 and will have a full IsoWeek and a complete month.
For example, January will end on 2nd February because the last full week of Jan "bit" from February. I tried a lot of solutions, like converting the ISOWeek to ISOMonth, but none gave the expected result.
Any help is appreciated.
Edit: Thanks to Alex below the solution is:
SELECT month(dateadd(d, 3, dateadd(WEEK, datediff(WEEK, 0, DATEADD(d,-1,'20200302')), 0)))
TheDate IsoWeek The Month---I want to add this
12/30/2019 1 1
12/31/2019 1 1
1/1/2020 1 1
1/2/2020 1 1
1/3/2020 1 1
1/4/2020 1 1
1/5/2020 1 1
1/6/2020 2 1
1/7/2020 2 1
1/8/2020 2 1
1/9/2020 2 1
1/10/2020 2 1
1/11/2020 2 1
1/12/2020 2 1
1/13/2020 3 1
1/14/2020 3 1
1/15/2020 3 1
1/16/2020 3 1
1/17/2020 3 1
1/18/2020 3 1
1/19/2020 3 1
1/20/2020 4 1
1/21/2020 4 1
1/22/2020 4 1
1/23/2020 4 1
1/24/2020 4 1
1/25/2020 4 1
1/26/2020 4 1
1/27/2020 5 1
1/28/2020 5 1
1/29/2020 5 1
1/30/2020 5 1
1/31/2020 5 1
2/1/2020 5 1
2/2/2020 5 1
2/3/2020 6 2
2/4/2020 6 2
2/5/2020 6 2
2/6/2020 6 2
2/7/2020 6 2
2/8/2020 6 2
2/9/2020 6 2
2/10/2020 7 2
2/11/2020 7 2
2/12/2020 7 2
2/13/2020 7 2
2/14/2020 7 2
2/15/2020 7 2
2/16/2020 7 2
2/17/2020 8 2
2/18/2020 8 2
2/19/2020 8 2
2/20/2020 8 2
2/21/2020 8 2
2/22/2020 8 2
2/23/2020 8 2
2/24/2020 9 2
2/25/2020 9 2
2/26/2020 9 2
2/27/2020 9 2
2/28/2020 9 2
2/29/2020 9 2
3/1/2020 9 2
3/2/2020 10 3
3/3/2020 10 3
3/4/2020 10 3
3/5/2020 10 3
3/6/2020 10 3
3/7/2020 10 3
3/8/2020 10 3
3/9/2020 11 3
3/10/2020 11 3
3/11/2020 11 3
3/12/2020 11 3
3/13/2020 11 3
3/14/2020 11 3
3/15/2020 11 3
3/16/2020 12 3
3/17/2020 12 3
3/18/2020 12 3
3/19/2020 12 3
3/20/2020 12 3
3/21/2020 12 3
3/22/2020 12 3
3/23/2020 13 3
3/24/2020 13 3
3/25/2020 13 3
3/26/2020 13 3
3/27/2020 13 3
3/28/2020 13 3
3/29/2020 13 3
3/30/2020 14 3
3/31/2020 14 3
4/1/2020 14 3
4/2/2020 14 3
4/3/2020 14 3
4/4/2020 14 3
4/5/2020 14 3
ISO weeks start on Mondays, therefore the month/year of a given week always equals the month/year on Thursday that week. You can get this by week-truncating your date column, then add 3 days, like here:
ALTER TABLE calendar ADD TheMonth int;
UPDATE calendar SET TheMonth = month(dateadd(DAY, 3, dateadd(WEEK, datediff(WEEK, 0, TheDate), 0)))
EDIT: I've subtracted a day to work
SELECT month(dateadd(d, 3, dateadd(WEEK, datediff(WEEK, 0, dateadd(d,-1,TheDate)), 0)))