Подтвердить что ты не робот

Получить первый день недели в SQL Server

Я пытаюсь группировать записи по неделям, сохраняя агрегированную дату как первый день недели. Тем не менее, стандартная техника, которую я использую для округления дат, кажется, работает некорректно с неделями (хотя она работает в течение дней, месяцев, лет, кварталов и любых других сроков, к которым я применил ее).

Вот SQL:

select "start_of_week" = dateadd(week, datediff(week, 0, getdate()), 0);

Это возвращает 2011-08-22 00:00:00.000, который является понедельником, а не воскресеньем. Выбор @@datefirst возвращает 7, который является кодом для воскресенья, поэтому сервер настроен правильно, насколько мне известно.

Я могу обойти это достаточно легко, изменив приведенный выше код на:

select "start_of_week" = dateadd(week, datediff(week, 0, getdate()), -1);

Но тот факт, что я должен сделать такое исключение, делает меня немного непростым. Кроме того, извините, если это дублирующий вопрос. Я нашел некоторые связанные вопросы, но никто не обращался к этому аспекту конкретно.

4b9b3361

ОТВЕТЫ

Ответ 1

Чтобы ответить, почему вы получаете понедельник, а не воскресенье:

Вы добавляете несколько недель к дате 0. Что такое дата 0? 1900-01-01. Какой был день 1900-01-01? Понедельник. Итак, в коде, который вы говорите, сколько недель прошло с понедельника, 1 января 1900 года? Позвольте называть это [n]. Хорошо, теперь добавьте [n] недель в понедельник, 1 января 1900 года. Не удивляйтесь, что это заканчивается в понедельник. DATEADD не имеет понятия, что вы хотите добавить несколько недель, но только до тех пор, пока вы не доберетесь до воскресенья, просто добавьте 7 дней, затем добавьте еще 7 дней... просто как DATEDIFF распознает только границы, которые были пересечены. Например, они возвращаются 1, хотя некоторые люди жалуются, что для округления вверх или вниз должна быть какая-то разумная логика:

SELECT DATEDIFF(YEAR, '2010-01-01', '2011-12-31');
SELECT DATEDIFF(YEAR, '2010-12-31', '2011-01-01');

Чтобы ответить, как получить воскресенье:

Если вы хотите воскресенья, выберите базовую дату, а не понедельник, а скорее воскресенье. Например:

DECLARE @dt DATE = '1905-01-01';
SELECT [start_of_week] = DATEADD(WEEK, DATEDIFF(WEEK, @dt, CURRENT_TIMESTAMP), @dt);

Это не будет нарушено, если вы измените настройку DATEFIRST (или ваш код будет запущен для пользователя с другой настройкой) - при условии, что вы все еще хотите воскресенья, независимо от текущей настройки. Если вы хотите, чтобы эти два ответа на jive, то вы должны использовать функцию, которая зависит от параметра DATEFIRST, например.

SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP);

Итак, если вы измените свой параметр DATEFIRST на понедельник, вторник, что у вас, поведение изменится. В зависимости от того, какое поведение вы хотите, вы можете использовать одну из следующих функций:

CREATE FUNCTION dbo.StartOfWeek1 -- always a Sunday
(
    @d DATE
)
RETURNS DATE
AS
BEGIN
    RETURN (SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @d), '19050101'));
END
GO

... или...

CREATE FUNCTION dbo.StartOfWeek2 -- always the DATEFIRST weekday
(
    @d DATE
)
RETURNS DATE
AS
BEGIN
    RETURN (SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, @d), @d));
END
GO

Теперь у вас есть много альтернатив, но какой из них лучше всего работает? Я был бы удивлен, если бы были какие-то существенные различия, но я собрал все ответы, предоставленные до сих пор, и провел их через два набора тестов - один дешевый и один дорогой. Я измерил статистику клиента, потому что я не вижу, чтобы I/O или память играли роль в производительности здесь (хотя они могут входить в игру в зависимости от того, как используется функция). В моих тестах результаты:

"Дешевый" запрос задания:

Function - client processing time / wait time on server replies / total exec time
Gandarez     - 330/2029/2359 - 0:23.6
me datefirst - 329/2123/2452 - 0:24.5
me Sunday    - 357/2158/2515 - 0:25.2
trailmax     - 364/2160/2524 - 0:25.2
Curt         - 424/2202/2626 - 0:26.3

"Дорогой" запрос на присваивание:

Function - client processing time / wait time on server replies / total exec time
Curt         - 1003/134158/135054 - 2:15
Gandarez     -  957/142919/143876 - 2:24
me Sunday    -  932/166817/165885 - 2:47
me datefirst -  939/171698/172637 - 2:53
trailmax     -  958/173174/174132 - 2:54

Я могу, при желании, передать детали моих тестов - останавливаясь здесь, так как это уже довольно длинное. Я был немного удивлен, увидев, что Курт вышел как самый быстрый в верхнем конце, учитывая количество вычислений и встроенный код. Возможно, я проведу более подробные тесты и блог об этом... если у вас нет возражений против публикации ваших функций в других местах.

Ответ 2

Для них, которые необходимо получить:

Понедельник = 1 и воскресенье = 7:

SELECT 1 + ((5 + DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7);

Воскресенье = 1 и суббота = 7:

SELECT 1 + ((6 + DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7);

Выше был аналогичный пример, но благодаря двойному "% 7" он был бы намного медленнее.

Ответ 3

Это чудесно работает для меня:

CREATE FUNCTION [dbo].[StartOfWeek]
(
  @INPUTDATE DATETIME
)
RETURNS DATETIME

AS
BEGIN
  -- THIS does not work in function.
  -- SET DATEFIRST 1 -- set monday to be the first day of week.

  DECLARE @DOW INT -- to store day of week
  SET @INPUTDATE = CONVERT(VARCHAR(10), @INPUTDATE, 111)
  SET @DOW = DATEPART(DW, @INPUTDATE)

  -- Magic convertion of monday to 1, tuesday to 2, etc.
  -- irrespect what SQL server thinks about start of the week.
  -- But here we have sunday marked as 0, but we fix this later.
  SET @DOW = (@DOW + @@DATEFIRST - 1) %7
  IF @DOW = 0 SET @DOW = 7 -- fix for sunday

  RETURN DATEADD(DD, 1 - @DOW,@INPUTDATE)

END

Ответ 4

Запустил этот script:

create function dbo.F_START_OF_WEEK
(
    @DATE           datetime,
    -- Sun = 1, Mon = 2, Tue = 3, Wed = 4
    -- Thu = 5, Fri = 6, Sat = 7
    -- Default to Sunday
    @WEEK_START_DAY     int = 1 
)
/*
Find the fisrt date on or before @DATE that matches 
day of week of @WEEK_START_DAY.
*/
returns     datetime
as
begin
declare  @START_OF_WEEK_DATE    datetime
declare  @FIRST_BOW     datetime

-- Check for valid day of week
if @WEEK_START_DAY between 1 and 7
    begin
    -- Find first day on or after 1753/1/1 (-53690)
    -- matching day of week of @WEEK_START_DAY
    -- 1753/1/1 is earliest possible SQL Server date.
    select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))
    -- Verify beginning of week not before 1753/1/1
    if @DATE >= @FIRST_BOW
        begin
        select @START_OF_WEEK_DATE = 
        dateadd(dd,(datediff(dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW)
        end
    end

return @START_OF_WEEK_DATE

end
go

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

Ответ 5

Для тех, кому нужен ответ на работе, а функция создания запрещена вашим администратором базы данных, будет работать следующее решение:

select *,
cast(DATEADD(day, -1*(DATEPART(WEEKDAY, YouDate)-1), YourDate) as DATE) as WeekStart
From.....

Это дает начало этой неделе. Здесь я предполагаю, что воскресенья - начало недель. Если вы считаете, что понедельник - это начало, вы должны использовать:

select *,
cast(DATEADD(day, -1*(DATEPART(WEEKDAY, YouDate)-2), YourDate) as DATE) as WeekStart
From.....

Ответ 6

Возможно, вам это нужно:

SELECT DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE())

или

DECLARE @MYDATE DATETIME
SET @MYDATE = '2011-08-23'
SELECT DATEADD(DD, 1 - DATEPART(DW, @MYDATE), @MYDATE)

Функция

CREATE FUNCTION [dbo].[GetFirstDayOfWeek]
( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
RETURN DATEADD(DD, 1 - DATEPART(DW, @pInputDate),
               @pInputDate)

END
GO

Ответ 7

CREATE FUNCTION dbo.fnFirstWorkingDayOfTheWeek
(
    @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) 

    DECLARE @wd  int =  1+(((@[email protected]) % 7)+5) % 7  -- this is always return Mon as 1,Tue as 2 ... Sun as 7 

    RETURN DATEADD(dd,[email protected],@currentDate) 

END

Ответ 8

Поскольку юлианская дата 0 - понедельник, просто добавьте количество недель в воскресенье которая за день до -1. выберите dateadd (wk, dateiff (wk, 0, getdate()), - 1)

Ответ 9

Set DateFirst 1;

Select 
    Datepart(wk, TimeByDay) [Week]
    ,Dateadd(d,
                CASE 
                WHEN  Datepart(dw, TimeByDay) = 1 then 0
                WHEN  Datepart(dw, TimeByDay) = 2 then -1
                WHEN  Datepart(dw, TimeByDay) = 3 then -2
                WHEN  Datepart(dw, TimeByDay) = 4 then -3
                WHEN  Datepart(dw, TimeByDay) = 5 then -4
                WHEN  Datepart(dw, TimeByDay) = 6 then -5
                WHEN  Datepart(dw, TimeByDay) = 7 then -6
                END
                , TimeByDay) as StartOfWeek

from TimeByDay_Tbl

Это моя логика. Установите первую неделю в понедельник, а затем подсчитайте, что такое день недели, когда дается день, а затем используйте DateAdd и Case I, чтобы рассчитать, какая дата была в предыдущий понедельник этой недели.

Ответ 10

Для основных (текущая неделя воскресенье)

select cast(dateadd(day,-(datepart(dw,getdate())-1),getdate()) as date)

Если на предыдущей неделе:

select cast(dateadd(day,-(datepart(dw,getdate())-1),getdate()) -7 as date)

Внутренне, мы создали функцию, которая делает это, но если вам нужно быстро и грязно, это сделает это.

Ответ 11

У меня нет никаких проблем с любым из приведенных здесь ответов, однако я считаю, что мой намного проще реализовать и понять. Я не выполнял никаких тестов производительности, но это должно быть небрежным.

Итак, я получил свой ответ из того, что даты хранятся на SQL-сервере как целые числа (я говорю только о компоненте даты). Если вы мне не верите, попробуйте этот SELECT CONVERT (INT, GETDATE()) и наоборот.

Теперь, зная это, вы можете сделать классные математические уравнения. Возможно, вы сможете придумать лучший, но вот мой.

/*
TAKEN FROM http://msdn.microsoft.com/en-us/library/ms181598.aspx
First day of the week is
1 -- Monday
2 -- Tuesday
3 -- Wednesday
4 -- Thursday
5 -- Friday
6 -- Saturday
7 (default, U.S. English) -- Sunday
*/

--Offset is required to compensate for the fact that my @@DATEFIRST setting is 7, the default. 
DECLARE @offSet int, @testDate datetime
SELECT @offSet = 1, @testDate = GETDATE()

SELECT CONVERT(DATETIME, CONVERT(INT, @testDate) - (DATEPART(WEEKDAY, @testDate) - @offSet))

Ответ 12

У меня была аналогичная проблема. Учитывая дату, я хотел получить дату в понедельник той недели.

Я использовал следующую логику: найдите номер дня на неделе в диапазоне 0-6, а затем вычтите это из даты начала.

Я использовал: DATEADD (день, - (DATEPART (будний день) + 5)% 7,)

Так как DATEPRRT (будний день) возвращает 1 = Sundaye... 7 = Суббота, DATEPART (будний день) + 5)% 7 возвращает 0 = понедельник... 6 = воскресенье.

Вычитание этого количества дней с исходной даты дает предыдущий понедельник. Тот же метод может быть использован для любого начального дня недели.

Ответ 13

Я нашел это простое и полезное. Работает, даже если первый день недели - воскресенье или понедельник.

DECLARE @BaseDate AS Date

SET @BaseDate = GETDATE()

DECLARE @FisrtDOW AS Date

SELECT @FirstDOW = DATEADD (d, DATEPART (WEEKDAY, @BaseDate) * -1 + 1, @BaseDate)

Ответ 14

Возможно, я здесь упрощаю, и это может быть так, но это, похоже, работает для меня. У вас еще не возникало проблем с этим...

CAST('1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR(30)) AS DATETIME) + (DATEPART(wk, YOUR_DATE) * 7 - 7) as 'FirstDayOfWeek'
CAST('1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR(30)) AS DATETIME) + (DATEPART(wk, YOUR_DATE) * 7) as 'LastDayOfWeek'