Frage Erhalten Sie den ersten Tag der Woche in SQL Server


Ich versuche, Datensätze nach Woche zu gruppieren und das aggregierte Datum als ersten Tag der Woche zu speichern. Die Standardtechnik, die ich für das Abrunden von Daten verwende, scheint jedoch mit Wochen nicht korrekt zu funktionieren (obwohl dies für Tage, Monate, Jahre, Quartale und jeden anderen Zeitrahmen gilt, auf den ich sie angewendet habe).

Hier ist das SQL:

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

Dies kehrt zurück 2011-08-22 00:00:00.000Das ist ein Montag, kein Sonntag. Auswählen @@datefirst kehrt zurück 7, das ist der Code für Sonntag, also ist der Server soweit ich weiß richtig eingerichtet.

Ich kann dies leicht genug umgehen, indem ich den obigen Code in:

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

Aber die Tatsache, dass ich eine solche Ausnahme machen muss, macht mich etwas unruhig. Ich entschuldige mich auch, wenn dies eine doppelte Frage ist. Ich habe einige verwandte Fragen, aber keine, die diesen Aspekt speziell angesprochen haben.


76
2017-08-23 23:50


Ursprung


Antworten:


Um zu antworten, warum Sie einen Montag und keinen Sonntag bekommen:

Sie fügen dem Datum eine Anzahl von Wochen hinzu. Was ist Datum 0? 1900-01-01. Wie war der Tag am 01.01.1900? Montag. In deinem Code sagst du also, wie viele Wochen seit Montag, dem 1. Januar 1900 vergangen sind? Nennen wir das [n]. Ok, jetzt fügen Sie [n] Wochen bis Montag, den 1. Januar 1900, hinzu. Sie sollten nicht überrascht sein, dass dies ein Montag ist. DATEADD hat keine Ahnung, dass du Wochen hinzufügen willst, aber nur bis du zu einem Sonntag kommst, es fügt nur 7 Tage hinzu und fügt dann 7 weitere Tage hinzu ... einfach so DATEDIFF erkennt nur Grenzen, die überschritten wurden. Zum Beispiel geben beide 1 zurück, obwohl sich einige Leute beschweren, dass es eine vernünftige Logik geben sollte, die eingebaut wird, um aufzurunden oder abzurunden:

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

Um zu beantworten wie man einen Sonntag bekommt:

Wenn Sie einen Sonntag wünschen, wählen Sie ein Basisdatum, das kein Montag, sondern ein Sonntag ist. Beispielsweise:

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

Dies wird nicht brechen, wenn Sie Ihre ändern DATEFIRST Einstellung (oder Ihr Code läuft für einen Benutzer mit einer anderen Einstellung) - vorausgesetzt, dass Sie trotz der aktuellen Einstellung immer noch einen Sonntag wünschen. Wenn Sie diese beiden Antworten möchten, sollten Sie eine Funktion verwenden, die tut abhängig von der DATEFIRST Einstellung z.B.

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

Also, wenn Sie Ihre ändern DATEFIRST Einstellung zu Montag, Dienstag, was hast du, das Verhalten wird sich ändern. Abhängig davon, welches Verhalten Sie wünschen, können Sie eine dieser Funktionen verwenden:

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

...oder...

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

Jetzt haben Sie viele Alternativen, aber welche ist die beste? Ich wäre überrascht, wenn es größere Unterschiede gäbe, aber ich sammelte alle bisher gegebenen Antworten und führte sie durch zwei Tests - eine billig und eine teuer. Ich habe die Client-Statistiken gemessen, weil ich hier weder I / O noch Speicher in der Performance sehen kann (obwohl diese je nach Verwendung der Funktion ins Spiel kommen können). In meinen Tests sind die Ergebnisse:

"Günstige" Zuordnungsabfrage:

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

"Teure" Zuordnungsabfrage:

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

Ich kann, wenn gewünscht, die Details meiner Tests weiterleiten - hier aufhören, denn das wird schon ziemlich umständlich. Ich war ein wenig überrascht zu sehen, dass Curt als der Schnellste am High-End herauskam, angesichts der Anzahl der Berechnungen und des Inline-Codes. Vielleicht werde ich ein paar gründlichere Tests durchführen und darüber bloggen ... wenn ihr keine Einwände dagegen habt, dass ich eure Funktionen anderswo veröffentliche.


119
2017-08-24 02:01



Für diejenigen, die bekommen müssen:

Montag = 1 und Sonntag = 7:

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

Sonntag = 1 und Samstag = 7:

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

Oben gab es ein ähnliches Beispiel, aber dank Double "% 7" wäre es viel langsamer.


11
2018-02-14 16:21



Das funktioniert wunderbar für mich:

ERSTELLEN FUNKTION [dbo]. [StartOfWeek]
(
  @INPUTDATE DATETIME
)
Gibt DATETIME zurück

WIE
START
  - Dies funktioniert nicht in Funktion.
  - SET DATEFIRST 1 - setze Montag auf den ersten Tag der Woche.

  DECLARE @DOW INT - um den Wochentag zu speichern
  SET @INPUTDATE = KONVERTIEREN (VARCHAR (10), @INPUTDATE, 111)
  SET @DOW = DATEPART (DW, @INPUTDATE)

  - Magieumwandlung von Montag auf 1, Dienstag auf 2 usw.
  - Unbeacht, was SQL Server über den Start der Woche denkt.
  - Aber hier haben wir Sonntag als 0 markiert, aber wir reparieren das später.
  SET @DOW = (@DOW + @@ DATEFIRST - 1)% 7
  IF @DOW = 0 SET @DOW = 7 - fix für Sonntag

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

ENDE

4
2017-08-24 00:12



Google hat dieses Skript gegooglet:

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


3
2017-08-23 23:54



Vielleicht brauchst du das:

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

Oder

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

Funktion

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

2
2017-08-24 00:01



ERSTELLEN FUNKTION dbo.fnFirstWorkingDayOfTheWeek
(
    @currentDate Datum
)
RÜCKKEHR INT
WIE
START
    - DATEFIRST-Einstellung erhalten
    DECLARE @ds int = @@ DATEFIRST
    - Holen Sie sich die Wochennummer unter der aktuellen DATEFIRST-Einstellung
    DECLARE @dow int = DATEPART (dw, @ currentDate)

    DECLARE @wd int = 1 + (((@ dow + @ ds)% 7) +5)% 7 - das ist immer Return Mo wie 1, Di wie 2 ... So wie 7

    RETURN DATEADD (TT, 1- @ wd, @ currentDate)

ENDE

2
2017-12-03 13:01



Für diejenigen, die die Antwort bei der Arbeit benötigen und das Erstellen von Funktionen von Ihrem DBA verboten ist, wird die folgende Lösung funktionieren:

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

Dies gibt den Beginn dieser Woche an. Hier gehe ich davon aus, dass Sonntage der Beginn von Wochen sind. Wenn Sie denken, dass Montag der Anfang ist, sollten Sie Folgendes verwenden:

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

2
2018-03-30 16:56



Da das Julianische Datum 0 ein Montag ist, addiere einfach die Anzahl der Wochen zum Sonntag was ist der Tag vor -1 Eg. wähle dateadd (wk, datediff (wk, 0, getdate ()), - 1)


0
2017-09-24 06:46



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

Das ist meine Logik. Stellen Sie die erste der Woche auf Montag ein und berechnen Sie dann den Wochentag, an dem ein Give Day ist. Dann wird mit DateAdd und Case berechnet, wie das Datum am vorherigen Montag dieser Woche gewesen wäre.


0
2018-05-21 04:07



Ich habe keine Probleme mit einer der hier gegebenen Antworten, aber meiner Meinung nach ist es viel einfacher zu implementieren und zu verstehen. Ich habe keine Leistungstests durchgeführt, aber es sollte vernachlässigbar sein.

So leitete ich meine Antwort von der Tatsache ab, dass Datumsangaben im SQL-Server als Ganzzahlen gespeichert sind (ich spreche nur von der Datumskomponente). Wenn Sie mir nicht glauben, versuchen Sie dies SELECT CONVERT (INT, GETDATE ()) und umgekehrt.

Jetzt wissen Sie das, Sie können einige coole mathematische Gleichungen machen. Sie könnten vielleicht eine bessere Idee haben, aber hier ist meine.

/*
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))

-1
2017-08-24 05:30



Ich hatte ein ähnliches Problem. Bei einem Termin wollte ich das Datum des Montag dieser Woche bekommen.

Ich habe die folgende Logik verwendet: Finde die Tagesnummer in der Woche im Bereich von 0-6 und subtrahiere sie dann vom Ursprungsdatum.

Ich benutzte: DATEADD (Tag, - (DATEPART (Wochentag,) + 5)% 7,)

Da DATEPRRT (Wochentag,) gibt 1 = Sundaye ... 7 = Samstag zurück, DATEPART (Wochentag,) + 5)% 7 gibt 0 = Montag ... 6 = Sonntag zurück.

Subtrahieren dieser Anzahl von Tagen vom ursprünglichen Datum gibt den vorherigen Montag. Die gleiche Technik könnte für jeden Starttag der Woche verwendet werden.


-1
2017-10-01 21:30