How to get week of year in SQL with calendar rule?

Question

In .NET C# there is the Calendar.GetWeekOfYear Method

public virtual int GetWeekOfYear(
DateTime time,
CalendarWeekRule rule,
DayOfWeek firstDayOfWeek)

where there is a parameter "CalendarWeekRule" to define the rules. In my case, I used CalendarWeekRule.FirstFourDayWeek to assert that a week needs to have 4 days in the same month to be considered a week.

https://msdn.microsoft.com/en-us/library/system.globalization.calendar.getweekofyear(v=vs.110).aspx

How can I do this in SQL? Is there an equivalent function?

FYI: ISO 8601 defines weeks of the year, and uses Monday as day 1 of the week, and stipulates that the week must have at least 4 days in the year it belongs to (so if 1st January falls on Mon-Thu, it belongs to the current year, but if it falls on Fri-Sun, it is part of the last week of the previous year, and it might be week 52 or week 53 of the previous year. Fundamentally, you have to get the 'day of year' value (1 for 1st January, 365 or 366 for 31st December), and also the 'day of week' of 1st January in the year, and dink around with arithmetic. — Jonathan Leffler, Nov 23 '17 at 16:13
Note [How do I calculate the week number given a date?](https://stackoverflow.com/questions/274861/how-do-i-calculate-the-week-number-given-a-date/275024#275024) — Jonathan Leffler, Nov 23 '17 at 16:15
@JonathanLeffler thank you. Is there an option to use Sunday as day 1 of the week instead of Monday? — Rute Lemos, Nov 23 '17 at 16:30

Fleury26 · Accepted Answer · 2017-11-23T16:37:46.987

6

DATEPART ( wk, date )

This will return an integer for the week from the date you provide. For further parameters of DATEPART here is the link! DATEPART TSQL

EDIT : As you asked in the comments for your question, you can change the first day of the week with SET DATEFIRST = (see values here)

edited Nov 23 '17 at 16:37

answered Nov 23 '17 at 16:14

Fleury26

585
4
19

Thank you, that was helpful – Rute Lemos Nov 23 '17 at 16:40
sql server uses the .net equivalent of System.Globalization.CalendarWeekRule.FirstDay – manit Aug 20 '19 at 20:53

score 1 · Answer 2 · answered Nov 23 '17 at 16:32

In SQL, If table is 'tblWeekOfYear' and [time] is a DateTime field, then it calculates the week no of the two different dates '2017-10-01' and '2017-10-02' in the DateTime field.

Select Distinct DatePart(ww, [time]) as WeekNo
from tblWeekOfYear where [time] between '2017-10-01' and '2017-10-02';

How to get week of year in SQL with calendar rule?

2 Answers2

Linked