SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Funkcje daty i czasu

Kolejną istotną grupą wbudowanych funkcji skalarnych są obiekty związane z przetwarzaniem typów danych daty i czasu.

YEAR ( data ), MONTH ( data ), DAY ( data ) – dokonują ekstraktu z daty, odpowiednio roku, miesiąca oraz dnia.

-- ekstrakt poszczególnych części daty
 
SELECT  YEAR ( '2013-02-12' ) as Rok,
        MONTH( '2013-02-12' ) as Miesiac,
        DAY  ( '2013-02-12' ) as Dzien

FN_datetime_01


Najczęściej stosowane funkcje zwracające date i czas systemowy to GETDATE() oraz SYSDATETIME(). Są to funkcje nie przyjmujące żadnych argumentów i zwracają po prostu bieżącą datę i czas systemowy.

-- funkcje zwracające aktualny czas i datę systemową
 
SELECT SYSDATETIME(), 
       SYSDATETIMEOFFSET(),
       GETDATE(),
       GETUTCDATE()

FN_datetime_02


DATEADD ( datepart, liczba, data ) – dodaje (lub odejmuje) liczbę jednostek daty/czasu określonych za pomocą datepart np dni (day, dd, d), lat (years,yy,yyyy), miesięcy (month,mm,m), minut (minute,mi,n) etc. do zadanej daty. Jednostki określone mogą być za pomocą pełnej nazwy, lub skrótu. Pełny ich opis znajdziesz tutaj. Stosowana często w warunkach filtracji, np. wszystkie zlecenia z ostatnich 14 dni. Funkcja DATEADD, jest też bardzo użyteczna w określaniu zakresów

-- DATEADD - dodawanie/odejmowanie jednostek określonego typu z zadanej daty
 
SELECT  DATEADD ( dd,-DAY( GETDATE()-1 ), GETDATE() ) as FirstDayCurrMonth,
	DATEADD ( dd,-DAY( GETDATE() ), GETDATE() ) as LastDayPrevMonth

FN_datetime_03


DATEDIFF ( datepart, startdate, enddate ) – różnica pomiędzy dwiema datami (end – start) wyrażona w jednostkach określonych przez datepart. Wiek pracowników :

USE Northwind
GO
 
-- DATEDIFF - określanie różnicy wyrażonej w konkretnych jednostkach 
-- pomiędzy dwiema datami
 
SELECT  FirstName, LastName, BirthDate, 	
	DATEDIFF ( yy , BirthDate , GETDATE() )  as Age
FROM dbo.Employees

FN_datetime_04


DATEPART( datepart, data ) – wyciąga określoną parametrem datepart, jednostkę podanej daty.

-- DATEPART - ekstrakt określonej części daty /czasu
 
SELECT  DATEPART( yy, GETDATE() ) as CurrentYear,
	DATEPART( mm, GETDATE() ) as CurrentMonth,
	DATEPART( dd, GETDATE() ) as CurrentDay,
	DATEPART( ww, GETDATE() ) as CurrentWeek

FN_datetime_05


DATENAME ( datepart, data ) – pododbna do DATEPART, zwraca wartość znakowa, określonej parametrem datepart, części daty w tym nazwę dnia tygodnia, miesiąca zgodnie z ustawieniami @@LANGID (bieżący język dla sesji)

SELECT  DATENAME(dw, GETDATE() ) as DzienTygodnia,
	DATENAME(mm, GETDATE() ) as Miesiac

FN_datetime_06


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

Liczba dni roboczych z danego zakresu dat

Odejmowanie dat

Zacznijmy od czegoś bardzo łatwego. Do wyznaczenia różnicy jako bezwzględnej liczby np. dni, miesięcy, lat pomiędzy dwoma datami możemy uzyć funkcji sql DATEDIFF. Funkcja zwraca wartość typu integer i jest to liczba jednostek takiego typu, jaki określimy w pierwszym argumencie funkcji. Aby określić np. różnicę w dniach pomiędzy dwoma datami – przekazujemy parametr day lub skrót dd ewentualnie jeszcze krócej d. Przykład :

-- przypisywanie wartości domyślnej w definicji zmiennej - SQL w wersji 2008 +
 
declare @StartDt smalldatetime = '2012-12-01'
declare @EndDt smalldatetime = '2012-12-21'
 
select DATEDIFF(dd,@StartDt,@EndDt) as dni, DATEDIFF(ww,@StartDt,@EndDt) as tygodni

Wynik :

dni         tygodni
----------- -----------
20          3

(1 row(s) affected)

Liczba dni roboczych pomiędzy dwoma datami

Czasem trzeba pokazać informację bardziej precyzyjną, np liczbę dni roboczych pomiędzy dwoma datami. Załóżmy że masz do zrobienia raport z informacją o zleceniach i czasie ich realizacji od momentu przyjęcia do wysłania. W  firmach w których pracowałem, interesujący był średni czas realizacji zleceń, zadań, kroków procesów (np. budowalanych – średni czas realizacji budowy stacji bazowej od momentu akwizycji działki pod budowę do momentu komercyjnego jej uruchomienia). Przykładów zastosowań znajdziemy mnóstwo. Jak zatem wykonać tego typu kalkulację ?

T-SQL oferuje szereg funkcji wbudowanych związanych z datą i czasem – oprócz DATEDIFF użyjemy do tego zadania DATENAME – funkcję zwaracjącą nazwę dnia tygodnia. Zakładam, że wszystkie soboty i niedziele są traktowane jako dni wolne.

declare @StartDate smalldatetime = '2012-12-01'
declare @EndDate smalldatetime = '2012-12-21'
 
-- liczba dni roboczych pomiędzy dwoma datami
SELECT (DATEDIFF(dd, @StartDate, @EndDate) + 1)
        -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
        -(CASE WHEN DATENAME(dw, @StartDate) =  DATENAME(dw,6) THEN 1 ELSE 0 END)
        -(CASE WHEN DATENAME(dw, @EndDate) =  DATENAME(dw,5) THEN 1 ELSE 0 END) 
       as WorkingDays

Powyższe zapytanie zwraca nam informację poprawną, ale tylko dla regularnych tygodni. Ponadto widać, że poprawność działania jest uzależniona od ustawień językowych sesji w ramach której łączymy się z bazą. W naszym przypadku zmienna sesji dot. języka jest ustawiona na us_english (operacje porównania do ‘Saturday’ i ‘Sunday’).
Żeby rozwiązać te problemy musimy oprócz dni wolnych by definition, uwględnić inne dni wolne czyli święta. Stworzymy w tym celu tabelę dbo.DniWolne, do której wrzucamy wszystkie extra dni wolne.

Aby uniezależnić się z kolei od ustawień językowych skorzystamy z funkcji DATENAME. Funkcja DATENAME nie jest wrażliwa na ustwienia zmiennej DATEFIRST (wbrew temu co można przeczytać w BOL), więc zawsze wskazuje na sobotę (5) i niedzielę (6). Typ wyliczeniowy dni tygodnia, z którego korzysta ta funkcja, obejmuje zakres od 0 (poniedziałek) do 6 (niedziela).

Ponieważ prawdopodobnie będziemy chcieli używać naszej kalkulacji w różnych miejscach, najlepiej zrobić z niej funkcję użytkownika (UDF User Defined Function), wtedy będziemy mogli używać jej w dowolnych (sensownych :)) miejscach kwerend i w innych obiektach w bazie (widoki, procedurki etc.). Rozwiązanie naszego problemu to :

-- tabela do przechowywania informacji o dniach wolnych
 
IF OBJECT_ID('dbo.DniWolne') is not null drop table dbo.DniWolne
GO
 
CREATE TABLE dbo.DniWolne(
	dtWolnyDzien smalldatetime NOT NULL Primary key clustered
);
 
-- Wrzucamy do tabeli wszystkie święta
INSERT INTO dbo.DniWolne
VALUES ('2012-12-25'),('2012-12-26'),('2013-01-01'),('2013-01-06')
-- itp itd
GO
 
-- Meritum czyli funkcja zwracająca liczbę dni roboczych pomiędzy dwoma datami 
-- z uwzględnieniem świąt zdefiniowanych w tabeli dbo.DniWolne
 
IF OBJECT_ID('dbo.fnWorkingDays') is not null drop function dbo.fnWorkingDays
GO
 
CREATE FUNCTION dbo.fnWorkingDays
(
	@StartDate datetime,
	@EndDate datetime
)
RETURNS int
AS
BEGIN
DECLARE @days int
 
DECLARE @SatName nvarchar(100),@SunName nvarchar(100)
 
SELECT @days = (DATEDIFF(dd, @StartDate, @EndDate) + 1)
        -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
        -(CASE WHEN DATENAME(dw, @StartDate) =  DATENAME(dw,6) THEN 1 ELSE 0 END)
        -(CASE WHEN DATENAME(dw, @EndDate) =  DATENAME(dw,5) THEN 1 ELSE 0 END) 
        - (SELECT COUNT(*) FROM dbo.DniWolne WHERE dtWolnyDzien BETWEEN @StartDate AND @EndDate 
		AND DATENAME(dw, dtWolnyDzien) <> DATENAME(dw,5) 
		AND DATENAME(dw, dtWolnyDzien) <> DATENAME(dw,6) )
 
RETURN (@days)
 
END
GO
 
-- no to jedziemy
select dbo.fnWorkingDays('2012-12-01','2012-12-28') as WorkingDays

Algorytm działania

  • bierzemy bezwzględną różnicę dni pomiędzy @StartDate a @EndDate – czyli funkcja DATEDIFF.
  • od liczby dni obliczonych w p.1 odejmujemy wszystkie soboty i niedziele występujące pomiedzy tymi datami – będzie to warunek opcjonalny, tylko wtedy gdy różnica w tygodniach pomiędzy datami jest większa niż 1.
  • dodatkowo konieczna korekta wynikająca ze sposobu obliczania różnicy w tygodniach pomiędzy datami a także, czy daty początku i końca przedziału nie wypadają np w sobotę lub niedzielę.

Uwagi

Stosując funkcje użytkownia, trzeba pamiętać, że będzie ona wykonywana dla każdego wiersza osobno, czyli tyle razy, ile wierszy zwracanych będzie przez nasze zapytanie (jeśli umieścimy ją w select). Jej definicja to proste przekształcenia na zmiennych + jedna kwerenda do małej tabelki z dniami wolnymi. Można ją jeszcze trochę optymalizować, np. poprzez dodanie kolumny wyliczeniowej IsWorkingDay w tabeli DniWolne, tak aby trochę uprościć kwerendę zliczającą dodatkowe dni wolne w zadanym okresie (niektóre dni wolne wypadają w sobotę lub niedzielę i dla tych, które się załapią w przedziale, badamy w funkcji czy brać je pod uwagę czy nie). Nie będzie to jednak znaczący wzrostu wydajności (stawiam, że żaden :)), z uwagi na charakter tabeli (mini) oraz niewlieką liczbę wierszy.

Ustawienia zmiennej sesyjnej dot. języka, można sprawdzić poleceniem np. DBCC USEROPTIONS
DBCC_USEROPTIONS
Ewenetualnie badając wartość zmiennej globalnej @@LANGID, która to przechowuje obowiązujące id języka. Informacje o językach zainstalowanych na naszym serwerze możemy wyciągnąć za pomocą widoku systemowego sys.syslanguages.