[解決済み] 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);

しかし、このような例外を作らなければならないというのは、少し不安です。また、これが重複した質問であれば申し訳ありません。関連する質問をいくつか見つけましたが、この点を具体的に取り上げているものはありませんでした。

どのように解決するのですか？

なぜ日曜日ではなく月曜日が表示されるのか、その理由をお答えします。

日付0に週数を追加しているのです。日付 0 とは何ですか? 1900-01-01. 1900-01-01の日は何日ですか？月曜日です。つまり、あなたのコードでは、1900年1月1日（月）から何週間が経過したかを示しているのです。これを[n]と呼ぶことにします。さて、1900年1月1日月曜日から[n]週を追加してください。これが月曜日になっても驚かないはずです。 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 の設定を変更しても (あるいは異なる設定を持つユーザに対してコードを実行しても) 壊れることはありません - ただし、現在の設定に関係なく日曜日が必要であることに変わりはありません。もし、この二つの答えが一致するようにしたいのであれば、次のような関数を使用すべきです。 を実行します。 に依存する関数を使うべきです。 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

さて、たくさんの選択肢がありますが、どれが一番性能が良いのでしょうか？大きな違いがあれば驚きますが、これまでに提供されたすべての回答を収集し、2 つのテスト セット (安価なものと高価なもの) でそれらを実行しました。クライアントの統計情報を測定したのは、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

"Expensive"割り当てクエリです。

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

必要であれば、私のテストの詳細を伝えることができますが、すでにかなり長文になっているのでここで止めておきます。計算の数とインラインコードを考えると、Curt'sがハイエンドで最速と出たのは少し驚きました。もっと徹底的なテストを行い、それについてブログを書くかもしれません...私が他の場所で関数を公開することに異議を唱えないのであれば。