Wrong Week Number Using Datepart in SQL Server

Wrong week number using DATEPART in SQL Server

You can do this within SQL 2008 very easily as it now supports isoww as the first datepart argument. However, this wasn't in SQL 2000 (or 2005). There is a function in this article which will do it for you in SQL 2000/2005.

In case the blog goes offline, here is the function. Go to the post to learn more about ISO and non-ISO weeks.

CREATE FUNCTION ISOweek  (@DATE datetime)
RETURNS int
AS
BEGIN
   DECLARE @ISOweek int
   SET @ISOweek= DATEPART(wk,@DATE)+1
      -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
   --Special cases: Jan 1-3 may belong to the previous year
   IF (@ISOweek=0) 
      SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
         AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
   --Special case: Dec 29-31 may belong to the next year
   IF ((DATEPART(mm,@DATE)=12) AND 
      ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
      SET @ISOweek=1
   RETURN(@ISOweek)
END

Wrong US week number calculation for 1st jan using datepart

If you want the US numbering, you can do this by taking the WEEK number of the end of the week rather than the date itself.

First ensure that the setting for first day of the week is in fact Sunday on your system. You can verify this by running SELECT @@DATEFIRST; this should return 7 for Sunday. If it doesn't, run SET DATEFIRST 7; first.

SELECT 
    end_of_week=DATEADD(DAY, 7-(DATEPART(WEEKDAY, '20151231')), '20151231'),
    week_day=DATEPART(WEEK, DATEADD(DAY, 7-(DATEPART(WEEKDAY, '20151231')), '20151231'));

Which will return 2016/01/02 - 1.

Incorrect ISO8601 Week Number using DatePart()

Using DateAdd and DatePart is notoriously bad at this sort of thing, but the issue is that there is a (bug) inconsistency in the way .NET calculates the week number.

See this page for more info ISO 8601 Week of Year format in Microsoft .Net

"Specifically ISO 8601 always has 7 day weeks. If the first partial week of a year doesn't contain Thursday, then it is counted as the last week of the previous year. Likewise, if the last week of the previous year doesn't contain Thursday then its[sic] treated like the first week of the next year. GetWeekOfYear() has the first behavior, but not the second"

This is some code I wrote to get the ISO 8601 week number based on the definition:

"Week number according to the ISO-8601 standard, weeks starting on Monday. The first week of the year is the week that contains that year's first Thursday (='First 4-day week'). The highest week number in a year is either 52 or 53."

''' <summary>
''' Finds the ISO 8601 Week Number based on a given date
''' </summary>
''' <param name="dateValue"></param>
''' <returns></returns>
''' <remarks>ISO 8601 Specifies Monday as the First Day of the week and week one defined as the First Four Day Week</remarks>
Public Shared Function GetIso8601WeekOfYear(ByVal dateValue As DateTime) As Integer
    Return GetWeekOfYear(dateValue, DayOfWeek.Sunday, CalendarWeekRule.FirstFourDayWeek)
End Function

It uses the following more generic methods to find the week of the year using the specifics of the ISO standard:

'Need a calendar - Culture's irrelevent since we specify start day of week
Private Shared cal As Calendar = CultureInfo.InvariantCulture.Calendar

''' <summary>
''' Returns the week number of the year based on the Last Day of the Week and the Week One Rule
''' </summary>
''' <param name="dateValue">The date to find the week number for</param>
''' <param name="lastDayOfWeek">The last day of the week</param>
''' <param name="rule">The Definition of Week One</param>
''' <returns>An integer specifying the week number in the year</returns>
''' <remarks></remarks>
Public Shared Function GetWeekOfYear(ByVal dateValue As DateTime, ByVal lastDayOfWeek As DayOfWeek, ByVal rule As CalendarWeekRule) As Integer
    'There is a bug in the .NET framework where some dates at the end of the year return the incorrect week number so to find the correct value we need to cheat.  
    'Find the DayOfWeek that represents the first day based on the last day
    Dim firstDayOfWeek As DayOfWeek = lastDayOfWeek.Increment
    'Find the date of the last day of the week, this ensures that we get the correct week number value
    dateValue = GetWeekendingDate(dateValue, lastDayOfWeek)
    'Return the value of the week for the last day of the week
    Return cal.GetWeekOfYear(dateValue, rule, firstDayOfWeek)
End Function

''' <summary>
''' Finds the week ending date for a specified date value
''' </summary>
''' <param name="dateValue">The date to find the week ending date for</param>
''' <param name="lastDayOfWeek">The last day of the week</param>
''' <returns>A date value that is the last day of the week that contains the specified dateValue</returns>
''' <remarks></remarks>
Public Shared Function GetWeekendingDate(ByVal dateValue As DateTime, ByVal lastDayOfWeek As DayOfWeek) As DateTime
    'Find out how many days difference from the date we are testing to the end of the week
    Dim dayOffset As Integer = lastDayOfWeek - cal.GetDayOfWeek(dateValue)
    If dayOffset < 0 Then dayOffset += 7
    'Add days to the test date so that it is the last day of the week
    Return dateValue.AddDays(dayOffset)
End Function

This code uses this extension method which is a quick lookup to find the next day of the week:

<Extension()>
Public Function Increment(ByVal aDay As DayOfWeek) As DayOfWeek
    Select Case aDay
        Case DayOfWeek.Sunday : Return DayOfWeek.Monday
        Case DayOfWeek.Monday : Return DayOfWeek.Tuesday
        Case DayOfWeek.Tuesday : Return DayOfWeek.Wednesday
        Case DayOfWeek.Wednesday : Return DayOfWeek.Thursday
        Case DayOfWeek.Thursday : Return DayOfWeek.Friday
        Case DayOfWeek.Friday : Return DayOfWeek.Saturday
        Case DayOfWeek.Saturday : Return DayOfWeek.Sunday
        Case Else : Return Nothing
    End Select
End Function

I tried your test cases with this and got the following:

Debug.WriteLine(GetIso8601WeekOfYear(Date.Parse("20/07/2015"))) '30
Debug.WriteLine(GetIso8601WeekOfYear(Date.Parse("09/03/2015"))) '11
Debug.WriteLine(GetIso8601WeekOfYear(Date.Parse("29/12/2014"))) '1
Debug.WriteLine(GetIso8601WeekOfYear(Date.Parse("05/01/2015"))) '2
Debug.WriteLine(GetIso8601WeekOfYear(Date.Parse("30/12/2013"))) '1
Debug.WriteLine(GetIso8601WeekOfYear(Date.Parse("06/01/2014"))) '2

Getting week number off a date in MS SQL Server 2005?

Be aware that there are differences in what is regarded the correct week number, depending on the culture. Week numbers depend on a couple of assumptions that differ from country to country, see Wikipedia article on the matter. There is an ISO standard (ISO 8601) that applies to week numbers.

The SQL server integrated DATEPART() function does not necessarily do The Right Thing. SQL Server assumes day 1 of week 1 would be January 1, for many applications that's wrong.

Calculating week numbers correctly is non-trivial, and different implementations can be found on the web. For example, there's an UDF that calculates the ISO week numbers from 1930-2030, being one among many others. You'll have to check what works for you.

This one is from Books Online (though you probably want to use the one from Jonas Lincoln's answer, the BOL version seems to be incorrect):

CREATE FUNCTION ISOweek  (@DATE DATETIME)
RETURNS INT
AS
BEGIN
   DECLARE @ISOweek INT
   SET @ISOweek = DATEPART(wk,@DATE) 
                  +1 
                  -DATEPART(wk,CAST(DATEPART(yy,@DATE) AS CHAR(4))+'0104')
   -- Special cases: Jan 1-3 may belong to the previous year
   IF (@ISOweek=0)
      SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy,@DATE) - 1
                     AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
   -- Special case: Dec 29-31 may belong to the next year
   IF ((DATEPART(mm,@DATE)=12) AND
      ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
      SET @ISOweek=1
   RETURN(@ISOweek)
END
GO

Get correct week number for december 31st

See the possible duplicate reference and a little tidy up:

declare @d datetime = '2018-12-31';
select cast(year(dateadd(day, 26 - datepart(ISO_WEEK, @d), @d)) as varchar(4)) + right(('0' + cast(datepart(ISO_WEEK, @d) as varchar(2))), 2)

results in 201901.

Test code:

select cast(ds.d as date) as 'date', cast(year(dateadd(day, 26 - datepart(ISO_WEEK, ds.d), ds.d)) as varchar(4)) + right(('0' + cast(datepart(ISO_WEEK, ds.d) as varchar(2))), 2) as "IsoWeek"
from (
    select dateadd(day, x.num, '2018-12-30') as d
    from (
        select h.num + d.num + s.num as num
        from (
            select 0 num union select 100 num union select 200 num union select 300 num
        ) h
        cross join (
            select 0 num union select 10 num union select 20 num union select 30 num
        ) d
        cross join (
            select 0 num union select 1 num union select 2 union select 3 num
        ) s
    ) x    
) ds
order by ds.d

which results in:

date        IsoWeek
2018-12-30  201852
2018-12-31  201901
2019-01-01  201901
2019-01-02  201901
2019-01-09  201902
2019-01-10  201902
2019-01-11  201902
2019-01-12  201902
2019-01-19  201903
2019-01-20  201903
2019-01-21  201904
2019-01-22  201904
2019-01-29  201905
2019-01-30  201905
2019-01-31  201905
2019-02-01  201905
2019-04-09  201915
2019-04-10  201915
2019-04-11  201915
2019-04-12  201915
2019-04-19  201916
2019-04-20  201916
2019-04-21  201916
2019-04-22  201917
2019-04-29  201918
2019-04-30  201918
2019-05-01  201918
2019-05-02  201918
2019-05-09  201919
2019-05-10  201919
2019-05-11  201919
2019-05-12  201919
2019-07-18  201929
2019-07-19  201929
2019-07-20  201929
2019-07-21  201929
2019-07-28  201930
2019-07-29  201931
2019-07-30  201931
2019-07-31  201931
2019-08-07  201932
2019-08-08  201932
2019-08-09  201932
2019-08-10  201932
2019-08-17  201933
2019-08-18  201933
2019-08-19  201934
2019-08-20  201934
2019-10-26  201943
2019-10-27  201943
2019-10-28  201944
2019-10-29  201944
2019-11-05  201945
2019-11-06  201945
2019-11-07  201945
2019-11-08  201945
2019-11-15  201946
2019-11-16  201946
2019-11-17  201946
2019-11-18  201947
2019-11-25  201948
2019-11-26  201948
2019-11-27  201948
2019-11-28  201948

Selecting week / ISO week number from a date/time field

The DATEPART function expects a date / datetime / datetime2 value. You are passing in an integer representing the day or month number.

Assuming you're storing your dates correctly, you just need to pass in the date value directly:

DATEPART(WEEK, Max(AuditHistory.ActionedDateTime)) As WeekNo

---

Related Topics