0

I am in the process of converting some MS Access queries into T-SQL (for use with SQL reporting services), and I've run into a problem.

Turns out in MS Access queries, there is a function called DATEPART that has two optional parameters (parameters that the corresponding T-SQL DATEPART function do not have): firstdayofweek and firstweekofyear. And I cannot figure out how to create the equivalent query in T-SQL.

Below is the original MS Access query (pared down to just the relevant data):

SELECT datepart("ww",OrderDate, 7, 2) AS WeekNumber FROM Orders;

Note: in this case, 7 means to calculate with Saturday as the first day of the week, and the 2 means to calculate the first week in the year as the first with 4 (or more) days.

How do I do this in T-SQL?

Scott
  • 123
  • 1
  • 1
  • 7
  • did you try this - https://msdn.microsoft.com/en-us/library/ms174420.aspx ? – Bulat Aug 11 '15 at 11:30
  • Yes, that's where I started. Unfortunately, that function doesn't take those two optional parameters (firstdayofweek and firstweekofyear), and the results are different. To give you an idea, I threw together a quick sample (one in access one in SQL) to see the results, and if I put in date 4/1/2014, access gives me week 13, SQL gives me week 14. But if I plug in date 4/1/2013, both give me 14, so it's not as simple as just subtracting one from the SQL result. – Scott Aug 11 '15 at 11:34
  • Did you see this? https://msdn.microsoft.com/en-us/library/ms181598.aspx – Bulat Aug 11 '15 at 11:38
  • That gets me part-way there - that does cover the part where it need to consider Saturday the start of the week. Unfortunately, it doesn't handle considering the "first" week to be the one with 4 or more days. – Scott Aug 11 '15 at 13:29

1 Answers1

2

The arguments you refer to are:

firstdayofweek

is omitted, Access assumes that Sunday is the first day of the week. This parameter can be one of the following values:

Constant    Value       Explanation
------------------------------------------------
vbUseSystem     0       Use the NLS API setting
vbSunday        1       Sunday (default)
vbMonday        2       Monday
vbTuesday       3       Tuesday
vbWednesday     4       Wednesday
vbThursday      5       Thursday
vbFriday        6       Friday
vbSaturday      7       Saturday

The equivalent in SQL Server SET DATEFIRST, note that this begins at Monday, not sunday, so the following would return 2 (since today is Tuesday);

SET DATEFIRST 1;
SELECT [WEEKDAY] = DATEPART(WEEKDAY, CAST('2015-08-11' AS DATE));

The next argument is:

firstweekofyear

Optional. It is a constant that specifies the first week of the year. If this parameter is omitted, Access assumes that the week containing Jan 1st is the first week of the year. This parameter can be one of the following values:

Constant            Value   Explanation
---------------------------------------------------------------------------
vbUseSystem         0       Use the NSL API setting
vbFirstJan1         1       Use the first week that includes Jan 1st (default)
vbFirstFourDays     2       Use the first week in the year that has at least 4 days
vbFirstFullWeek     3       Use the first full week of the year

The standard DATEPART(WEEK, @Date) is the equivalent of vbFirstJan1, this will reset the week number at the first of Jan, then thereafter increment by 1 at each passing of whatever is set as DATEFIRST.

The clostest equivalent to vbFirstFourDays is to use DATEPART(ISO_WEEK, @Date), the ISO Standard defines the first week of the year as the first week to contain 4 days, this is not quite equivalent because this is not influenced by DATEFIRST, the ISO standard defines the week as Monday to Sunday, therefore ISO_WEEK also does, regardless of DATEFIRST or regional settings.

There is no equivalent at all to vbFirstFullWeek.

I have found it is usually business logic that dictactes any deviation from these standard definitions of week numbers, or day numbers, as such this is almost always best handled by building yourself a calendar table, you can then add your new column to it, and run a one off update. For example, I have had to report on weeks running from Thursday to Wednesday for commission payments, where the first week of the year was defined as the first full week (so in access this would be DATEPART("ww", [Date], 4, 5)), with no equivalent function in MS SQL I added a new column to the calendar table PaperSalesCommissionWeekNumber, then I could just join to my calendar table to get the week number:

SELECT  c.PaperSalesCommissionWeekNumber
FROM    dbo.Deal AS d
        INNER JOIN dbo.Calender AS c
            ON d.DealDate = c.DateKey;
GarethD
  • 68,045
  • 10
  • 83
  • 123