SQL DatePart Week, year to reflect fiscal year

Question

I currently have a report that uses DATEPART to return the week a 'car' was returned. However, the business I work for fiscal year starts first Sunday of the year (this instance commencing 03/01/2016 would be week 1). However, using SQL 'DATEPART wk' would return this date is week 2:

Current outcome using DATEPART (wk, year etc):

CarTitle      ReturnDate      Year       Week       
Car 1         30/12/2015      2015        53
Car 2         02/01/2016      2016        1
Car 3         03/01/2016      2016        2
Car 4         05/01/2016      2016        2
Car 5         10/01/2016      2016        3
Car 6         17/01/2016      2016        4

Example of expected outcome:

CarTitle      ReturnDate      Year       Week       
Car 1         30/12/2015      2015        53
Car 2         02/01/2016      2015        53
Car 3         03/01/2016      2016        1
Car 4         05/01/2016      2016        1
Car 5         10/01/2016      2016        2
Car 6         17/01/2016      2016        3

Answer 1

I can calculate this with the following SQL:

SELECT
    CarTitle,
    DATEPART(ISO_WEEK, DATEADD(day, 1, ReturnDate))
FROM
    dbo.My_Table

However, this depends on your localization settings (which are probably different from mine given how you express dates) and if you use this code throughout your system and then the business decides to change it's method for calculating weeks then you could end up with a big refactorization headache.

I prefer to use a Calendar table for this sort of thing:

CREATE TABLE dbo.Calendar (
    calendar_date    DATE        NOT NULL,
    week_number      SMALLINT    NOT NULL,
    is_holiday       BIT         NOT NULL,
    name             VARCHAR(30) NOT NULL,  -- i.e. 'January 6th, 2016'
    CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (calendar_date)
)

You can expand the table as needed, for example if accounting uses a different definition for week of the year than another department then that's easy to accommodate by adding another column. This then makes your query a simple join to get the week number:

SELECT
    MT.CarTitle,
    MT.ReturnDate,
    CAL.week_number
FROM
    My_Table MT
INNER JOIN dbo.Calendar CAL ON CAL.calendar_date = MT.ReturnDate

You'll need to populate the table, but that should be a one time effort (populate it for 50 years in advance and it's still a pretty small table) and you can use code similar to my first statement to generate the values - don't enter each date by hand ;) After that you just need to maintain the table if the business logic changes (which is easier than changing all of your queries that deal with dates) or if you get a new requirement that needs a new column.