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

user_2397424 0
Viewed 7136

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?

calendar date datetime sql-server sql
2 Answers
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)

user_88630310

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';
user_44642200
Related