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