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
.
I've found a couple of articles that helped me answer to derive an answer to this question:
Deterministic scalar function to get week of year for a date
http://sqlmag.com/t-sql/datetime-calculations-part-3
It may be possible to simplify this UDF, but it gives me exactly what I was looking for:
Then, executing the following statements would return 35 and 36 respectively: