SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Pierwszy / ostatni dzień tygodnia, miesiąca

Początek i koniec tygodnia

Zdarza się, że na podstawie podanej daty, chcemy wyznaczyc np. pierwszy dzień tygodnia w którym ta data się mieści. Aby to wykonać, możemy posłużyć się podstawowymi funkcjami związanymi z datą i czasem :

-- początek tygodnia - zawsze poniedziałek - niezależne od datefirst 
SELECT DATEADD(wk, DATEDIFF(d, 0, GETDATE()) / 7, 0)
 
-- pierwszy dzień bieżącego tygodnia - zależne od ustawień @@Datefirst 
SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE());

W pierwszym przypadku wyznaczamy bezwzględną liczbę tygodni od daty 0 (1900-01-01) do dziś. Następnie dodajemy, znów do daty 0, zminiejszając dokładność do jednego tygodnia (czyli do jednostki na której nam zależy). Żadna z użytych funkcji nie jest wrażliwa od ustawień zmiennej @@DateFirst dlatego wynik zawsze będzie wyznaczał nam poniedzałek.

Drugi przypadek – wyznaczamy numer dnia tygodnia z zadanej daty (będzie to liczba 1-7) za pomocą funkcji DATEPART i odejmujemy (no dobrze, dodajemy z minusem :)) do zadanej danty+1. Ponieważ funkcja DATEPART z parametrem weekday jest wrażliwa na ustawienia zmiennej @@DateFirst – wynik funkcji będzie zależny od ustawień serwera. Aby zmienić sposób interpretacji pierwszego dnia tygodnia – set firstday n (n z <1-7>).

Początek i koniec miesiąca

Na podobnej, prostej zasadzie możemy wyznaczyc pierwszy dzień bieżącego miesiąca i ostani poprzedniego. Za pomocą Funkcji DAY(data) otrzymamy info o numerze dnia z danego miesiąca. Teraz pozostaje jedynie odjąć (dodać z minusem :)) tą wartość jako liczbę dni od zadanej daty :

 -- informacja o dniu miesiąca 
select DAY(getdate())
 
-- pierwszy dzień bieżącego miesiąca
select DATEADD(dd,-(DAY(getdate())-1), getdate()) 
 
 -- ostatni dzień poprzedniego miesiąca
select DATEADD(dd,-DAY(getdate()), getdate())

Koniec bieżącego miesiąca? Nic prostszego, wystarczy posłużyć się tym co już znamy i dodać do daty miesiąc, odejmowanie dni bez zmian :

-- ostatni dzień bieżącego miesiąca
SELECT DATEADD(mm,1,DATEADD(dd,-(DAY(getdate())-1), getdate()) )-1
 
-- i jeszcze pierwszy dzień poprzedniego miesiąca wersja sprostowana.
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate()) - 1, 0)

Wszędzie tam gdzie jest zastosowana funkcja getdate(), możemy umieścić nazwę kolumny, której dane chcemy przekształcać. Na podobnej zasadzie możemy wyznaczyć pierwszy lub ostatni dzień roku.
Na koniec może jezcze jeden przykład – jak wyznaczyć pierwszy poniedziałek danego miesiąca :

select DATEADD(wk,DATEDIFF(wk,0,DATEADD(dd,6-DATEPART(day,getdate()),getdate())),0)

Oczywiście jeśli chcemy wykorzystywać tego typu kalkulacje wiele razy, w różnych miejscach – najlepiej zapisać je w postaci funkcji skalarnych UDF co znacznie upraszcza nasze zapytanka.

Koniec miesiąca w SQL Server 2012 – nowa funkcja EOMONTH()

W SQL Server 2012 pojawia się nowa funkcja związana z datą i czasem – EOMONTH() która względem podanej daty zwraca ostatni dzień miesiąca w którym ta data się zawiera. Realizuje nam zatem zadanie, które wykonaliśmy za pomocą funkcji DATEADD.

-- nowa funkcja EOMONTH() - wprowadzona w SQL Server 2012
select EOMONTH(getdate()) LastDayOfMonth, getdate() CurrentDate
LastDayOfMonth CurrentDate
-------------- -----------------------
2013-01-31     2013-01-17 12:46:04.803

(1 row(s) affected)

Errata

W metodzie obliczania pierwszego dnia poprzedniego miesiąca, wkradł się błąd. Z pewnością nie byłoby go, gdybym przynajmniej przetestował tą metodę w pełnym zakresie (i nie ma co zwalać winy na przerwę świąteczną, w której pisałem tego posta :)). Poniżej prosty skrypcik, pokazujący błędne myślenie – różnica pomiedzy metodą błędną i tą, sprostowaną przez @paazur :

declare @Date smalldatetime  
 
create table #temp
(
	data smalldatetime,
	FirstDayLastMonth smalldatetime,
	FirstDayLastMonthWRONG smalldatetime,
)
 
set @Date = '2013-01-01'
 
while(YEAR(@Date)<'2014')
begin
 
	insert into #temp 
	values(@Date,
		-- correct method
		DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) - 1, 0),
		-- wrong method
		DATEADD(dd,-DAY(@Date)+1,DATEADD(mm,-1,@Date)))
 
	set  @Date = DATEADD(DD,1,@Date)
 
end
 
select * from #temp
where FirstDayLastMonth <> FirstDayLastMonthWRONG
 
drop table #temp

8 thoughts on “Pierwszy / ostatni dzień tygodnia, miesiąca

  1. Witam
    Jeśli chodzi o ‚pierwszy dzień poprzedniego miesiąca’ to jest błąd w przypadku gdy mamy np. 29,30 lub 31 marca wtedy jako ten dzień wyskakuje na 31,30 lub 31 styczeń. ja to rozwiązałem tak :
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) – 1, 0)
    Pozdrawiam

    • Zgadza się – błąd był i to nawet szerszy… Dzięki za czujność, z pewnością nie testowałem tego skryptu w tych dniach :) :
      yyyy-03-29 00:00:00
      yyyy-03-30 00:00:00
      yyyy-03-31 00:00:00
      yyyy-05-31 00:00:00
      yyyy-07-31 00:00:00
      yyyy-10-31 00:00:00
      yyyy-12-31 00:00:00

      Dodałem erratę tego posta z porównaniem błędnej i prawidłowej wersji metody obliczania pierwszego dnia poprzedniego miesiąca.

  2. Witam
    Jeszcze jedno w pierwszym dniu bierzącego miesiąca też jest mały błąd w przypadku daty, gdy GETDATE jest ustawiony na pierwszy dzień miesiąca to pokazuje nam miesiąc wczesniej ja rozwiązałem to tak
    cast(convert(nvarchar(6),@date,112)+ ’01’ as datetime)
    Pozdrawiam

    • Można powiedzieć, że błąd nie tyle w samej metodzie co w zlym postawieniu nawiasów :) co nie zmienia faktu, że wynik był blędny w sytuacji gdy Day(@x)=1. Poniżej porównanie dwóch działań, z tą drobną różnicą …

      declare @data smalldatetime
      set @data = '2013-04-01'
       
      SELECT 
      -- wrong 
      DATEADD(dd,-DAY(@data-1), @data),
      -- correct
      DATEADD(dd,-(DAY(@data)-1), @data)

      Przyznam się bez bicia, że w moim poście był jeszcze jeden błąd :) – w wyznaczaniu początku tygodnia jako poniedzialku, niezależnie od DATEFIRST.

      -- LAST MONDAY
      declare @data smalldatetime
      set @data = '2013-04-07'
       
      -- wrong - result tomorrow : 2013-04-08
      SELECT DATEADD(ww, DATEDIFF(ww,0,@data), 0) 
      -- correct -- will be : 2013-04-01
      SELECT DATEADD(wk, DATEDIFF(d, 0, @data) / 7, 0)
  3. Brakuje informacji, że pierwsza metoda wyznaczania pierwszego dnia tygodnia działa, ponieważ data 0 czyli 1900-01-01 to poniedziałek. Równie dobrze zamiast daty 0 można podać 2016-10-03.

    • Trafna uwaga, czyli jak chcemy wyznaczyć np. miniony (najbliższy lub równy) wtorek dla zadanej daty to wybieramy sobie jakiś wtorek historycznie odległy aby pokrywał nasz zakres i mamy :

      -- '20160927' to wtorek
      SELECT DATEADD(wk, DATEDIFF(d, '20160927',  getdate() ) / 7, '20160927')

      Dzięki za komentarz i pozdrawiam !

Leave a comment

Twój adres email nie zostanie opublikowany.

Uzupełnij równanie (SPAM protection) *