Преобразование запроса MS Access в T-SQL - функция datepart

Question

Я нахожусь в процессе преобразования некоторых запросов MS Access в T-SQL (для использования со службами отчетов SQL) и столкнулся с проблемой.

Оказывается, в запросах MS Access есть функция DATEPART, которая имеет два необязательных параметра (параметры, которых нет у соответствующей функции T-SQL DATEPART): firstdayofweek и firstweekofyear. И я не могу понять, как создать эквивалентный запрос в T-SQL.

Ниже приведен исходный запрос MS Access (сводится только к соответствующим данным):

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

Примечание: в этом случае 7 означает вычисление с субботой в качестве первого дня недели, а 2 означает вычисление первой недели в году как первой с 4 (или более) днями.

Как мне сделать это в T-SQL?

Answer 1

Аргументы, на которые вы ссылаетесь:

firstdayofweek

опущено, Access предполагает, что воскресенье - первый день недели. Этот параметр может принимать одно из следующих значений:

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

Эквивалент в SQL Server SET DATEFIRST Обратите внимание, что это начинается в понедельник, а не в воскресенье, поэтому следующее вернет 2 (так как сегодня вторник);

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

Следующий аргумент:

firstweekofyear

Необязательный. Это константа, которая указывает первую неделю года. Если этот параметр пропущен, Access предполагает, что неделя, содержащая 1 января, является первой неделей года. Этот параметр может принимать одно из следующих значений:

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

Стандарт DATEPART(WEEK, @Date) является эквивалентом vbFirstJan1, это будет сбрасывать номер недели первого января, а затем увеличивать на 1 при каждом прохождении того, что установлено как DATEFIRST,

Самый близкий эквивалент vbFirstFourDays это использовать DATEPART(ISO_WEEK, @Date) Стандарт ISO определяет первую неделю года как первую неделю, содержащую 4 дня, это не совсем эквивалентно, поскольку на нее не влияют DATEFIRST стандарт ISO определяет неделю с понедельника по воскресенье, поэтому ISO_WEEK также, независимо от DATEFIRST или региональные настройки.

Там нет эквивалента вообще vbFirstFullWeek,

Я обнаружил, что обычно бизнес-логика диктует любое отклонение от этих стандартных определений номеров недель или номеров дней, так как это почти всегда лучше всего сделать, создав собственную таблицу календаря, затем вы можете добавить к ней новый столбец и запустить одноразовое обновление. Например, мне приходилось сообщать о неделях с четверга по среду для комиссионных платежей, где первая неделя года была определена как первая полная неделя (поэтому при доступе это будет DATEPART("ww", [Date], 4, 5)), без эквивалентной функции в MS SQL я добавил новый столбец в таблицу календаря PaperSalesCommissionWeekNumber тогда я могу просто присоединиться к моей таблице календаря, чтобы получить номер недели:

SELECT  c.PaperSalesCommissionWeekNumber
FROM    dbo.Deal AS d
        INNER JOIN dbo.Calender AS c
            ON d.DealDate = c.DateKey;