SQL Server UDF for getting week of year, with first day of week argument

Question

I'm looking for a SQL Server UDF that will be equivalent to DATEPART(WEEK, @date), but will allow the caller to specify the first day of the week. Somewhat similar to MySql's WEEK function. E.g.:

CREATE FUNCTION Week (@date date, @firstdayofweek int)
RETURNS int
BEGIN
  -- return result would be the same as:
  --   SET DATEFIRST @firstdayofweek
  --   DATEPART(WEEK, @date)
END

My application does not have the opportunity to call SET DATEFIRST.

Examples:

SELECT Week('2013-08-28', 2) -- returns 35
SELECT Week('2013-08-28', 3) -- returns 36

The above results would always be the same, regardless of SQL Server's value for @@DATEFIRST.

How about 2013-01-01 and 1? Also, does 1 represent Sunday or Monday? — Dan Bracuk, Aug 28 '13 at 19:38
1. You can check your result against the script: SET DATEFIRST 1; SELECT DATEPART(WEEK, '2013-01-01'); — Brad R, Aug 28 '13 at 19:42
1 represents Monday. Sunday is 7. DATEFIRST doc: http://technet.microsoft.com/en-us/library/ms181598.aspx — Brad R, Aug 28 '13 at 19:45
Can you give a couple examples and explain why they return what they do? I am unclear to what `@firstdayofweek` does for the function. — Khan, Aug 28 '13 at 19:49

score 0 · Answer 1 · answered Aug 28 '13 at 20:32

0

You could use something like this:

DATEPART(WEEK, DATEADD(DAY, 8 - @firstdayofweek, @date))

Instead of moving the first day of the week you are moving the actual date. Using this formula the first day of the week would be set using the same number values for days that MS SQL Server uses. (Sunday = 1, Saturday = 7)

answered Aug 28 '13 at 20:32

jellomonkey

1,954
15
15

Shifting the day will not work because you could cross a week boundary . For instance, DATEPART(WEEK, '2013-08-31') returns 35 (@@DATEFIRST = 7) and DATEPART(WEEK, DATEADD(DAY, 8 - 1, '2013-08'31')) returns 36. – Brad R Aug 28 '13 at 22:11
I'm sorry your example above isn't clear. If the first day of the week is day 7 then DATEPART(WEEK, DATEADD(DAY, 8-7, '2013-08-31')) will return 36 because you just said the first day of the week is Saturday and August 31 of 2013 is the 36th Saturday of 2013. So it works correctly. Perhaps you are trying to solve a different problem? – jellomonkey Aug 30 '13 at 14:32
Some additional clarity, the datepart WEEK is not affected by the DATEFIRST setting in MS SQL Server. I can't find any specific documentation but I have tested it. The week is the same regardless of what the first day of the week is. Also, I had said 8-31-2013 was the 36th Saturday when I should have said it marks the beginning of the 36th week and is actually the 35th Saturday. – jellomonkey Aug 30 '13 at 14:42
My examples above are correct. DATEPART does rely on DATEFIRST. "When datepart is week (wk, ww) or weekday (dw), the return value depends on the value that is set by using SET DATEFIRST." (http://msdn.microsoft.com/en-us/library/ms174420.aspx) – Brad R Sep 10 '13 at 14:45

score 0 · Accepted Answer · edited May 23 '17 at 11:57

I've found a couple of articles that helped me answer to derive an answer to this question:

It may be possible to simplify this UDF, but it gives me exactly what I was looking for:

CREATE FUNCTION Week (@date DATETIME, @dateFirst INT)
RETURNS INT
BEGIN
  DECLARE @normalizedWeekOfYear INT = DATEDIFF(WEEK, DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), 0), @date) + 1
  DECLARE @jan1DayOfWeek INT = DATEPART(WEEKDAY, DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), 0) + @@DATEFIRST- 7) - 1
  DECLARE @dateDayOfWeek INT = DATEPART(WEEKDAY, DATEADD(DAY, @@DATEFIRST- 7, @date)) - 1

  RETURN @normalizedWeekOfYear + 
    CASE
      WHEN @jan1DayOfWeek < @dateFirst AND @dateDayOfWeek >= @dateFirst THEN 1
      WHEN @jan1DayOfWeek >= @dateFirst AND @dateDayOfWeek < @dateFirst THEN -1
      ELSE 0
    END
END
GO

Then, executing the following statements would return 35 and 36 respectively:

SELECT dbo.Week('2013-08-28', 2)
SELECT dbo.Week('2013-08-28', 3)

score 0 · Answer 3 · answered Dec 03 '13 at 12:48

0

/*
No matter how @@DATEFIRST is
return result as
weekdayName,weekdayNumber
Mo  1
Tu  2
Wn  3
Th  4
Fr  5
Sa  6
Su  7
*/

CREATE FUNCTION dbo.fnFixWeekday
(
    @currentDate date
)
RETURNS INT
AS
BEGIN
    -- get DATEFIRST setting
    DECLARE @ds int = @@DATEFIRST 
    -- get week day number under current DATEFIRST setting
    DECLARE @dow int = DATEPART(dw,@currentDate) 

    RETURN 1+(((@dow+@ds) % 7)+5) % 7

END

answered Dec 03 '13 at 12:48

JG JIN

21
1

This does not answer my question. The requirement is that the function must take the first day of week as one of the arguments. – Brad R Dec 04 '13 at 17:21

SQL Server UDF for getting week of year, with first day of week argument

3 Answers3