SQL SERVER – Find Week of the Year Using DatePart Function

The DATEPART function returns a week number according to its week definition. I choose that convention before using calendar logic.

I plan blog ideas in a calendar, usually one week at a time. Readers asked how I organized daily posts. The week number and its first and last dates helped that planning.

DECLARE @Date date = '20160904';
DECLARE @OriginalDateFirst int = @@DATEFIRST;
SET DATEFIRST 7; -- Sunday starts this demonstration week.
SELECT DATEPART(week, @Date) AS WeekOfYear,
       CONVERT(char(10), DATEADD(day, 1 - DATEPART(weekday, @Date), @Date), 111) AS FirstDateOfWeek,
       CONVERT(char(10), DATEADD(day, 7 - DATEPART(weekday, @Date), @Date), 111) AS LastDateOfWeek,
       DATEPART(iso_week, @Date) AS ISOWeekNumber;
SET DATEFIRST @OriginalDateFirst;

The example deliberately uses a fixed date and a Sunday-based week. Replace @Date with CONVERT(date, SYSDATETIME()) for the current server date. Saving and restoring DATEFIRST avoids leaving this demonstration setting behind in the connection. Style 111 displays dates as yyyy/mm/dd.

DATEPART(week) places January 1 in week 1 and depends on DATEFIRST. DATEPART(iso_week) uses ISO rules. An ISO week can belong to a neighboring ISO year. The ordinary calendar year doesn’t always supply its correct year label.

The date type and SYSDATETIME require SQL Server 2008 or later. CONVERT formatting doesn’t remove that requirement. Decide whether your application needs Sunday-based, Monday-based or ISO weeks.

Reference: DATEPART week and ISO week rules.

A week number is not a complete calendar label, it is a value that needs its week and year convention.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.