1. Funkcje daty i czasu w SQL Server – Jak z nich korzystać?
W artykule lista wszystkich funkcji daty i czasu w SQL Server. Mamy do dyspozycji ponad 20 funkcji, które dają nam możliwość manipulacji datą i czasem. Funkcje daty i czasu w SQL Server możemy podzielić na kilka grup. Są to:
- Funkcje wyciągające elementy z daty i czasu
- Funkcje do tworzenia dat i czasu
- Funkcje zwracające różnice pomiędzy datami
- Funkcje zwracające bieżącą datę i czas
- Funkcje manipulujące datą i czasem
- Funkcje logiczne i systemowe daty i czasu
- Funkcje konwertujące datę i czas
Do manipulacji niektórymi funkcjami daty i czasu używa się argumentu datepart. Poniżej tabelka z objaśnieniem argumentu. Będzie Ci potrzeba w przykładach używanych w rozdziale.
| Nazwa_pl | Nazwa_en | Skrót |
|---|---|---|
| rok | year | yy, yyyy |
| miesiąc | quarter | qq, q |
| dzień | month | mm, m |
| kolejny dzień roku | dayofyear | dy, y |
| kolejny dzień miesiąca | day | dd, d |
| kolejny numer tygodnia | week | wk, ww |
| numer dnia tygodnia | weekday | dw |
| godzina | hour | hh |
| minuta | minute | mi, n |
| sekunda | second | ss, s |
| milisekunda | millisecond | ms |
| mikrosekunda | microsecond | mcs |
| nanosekunda | nanosecond | ns |
| strefa czasowa | TZoffset | tz |
| iso week | ISO_WEEK | isowk, isoww |
2. Funkcje wyciągające elementy z daty i czasu
Poniżej lista funkcji daty i czasu w SQL Server, za pomocą których możemy wyciągnąć z dat takie elementy jak np dzień, miesiąc, rok itp.:
| Funkcja | Składnia | Opis | Typ zwracany |
|---|---|---|---|
| DAY | DAY ( date ) | Zwraca dzień z daty w formie liczby od 1 do 31. | int |
| MONTH | MONTH ( date ) | Zwraca miesiąc z daty w formie liczby od 1 do 12. | int |
| YEAR | YEAR ( date ) | Zwraca rok z daty w formie liczby. | int |
| DATENAME | DATENAME ( datepart , date ) | Zwraca z daty element określony w argumencie datepart w formie słownej. | nvarchar |
| DATEPART | DATEPART ( datepart , date ) | Zwraca z daty element określony w argumencie datepart. Np. rok, miesiąc, sekunda itp. | int |
Stwórzmy kilka przykładów z użyciem poznanych funkcji. Zauważ, że przykładowa data została wprowadzona w dwóch różnych formach: YYYYMMDD i YYYY-MM-DD. W dwóch ostatnich funkcjach używany jest interwał datepart. Nie należy go mylić z funkcją o tej samej nazwie. Interwał możemy wprowadzać w cudzysłowie.
--tomaszkenig.pl
select
DAY('2020-01-01 12:23:11.1234567') as f_DAY,
MONTH('2020-01-01') as f_MONTH,
YEAR('20200101') as f_YEAR,
DATENAME("MM" , '20200101') as f_DATENAME,
DATEPART("isowk" , '20200101') as f_DATEPART_1,
DATEPART(HH , '2020-01-01 12:23:11.1234567') as f_DATEPART_2,
DATEPART(MI , '2020-01-01 12:23:11.1234567') as f_DATEPART_3,
DATEPART(DD , '2020-01-01 12:23:11.1234567') as f_DATEPART_4
;
3. Funkcje do tworzenia dat i czasu
W SQL Server mamy szereg funkcji, z których możesz utworzyć wartość o typie danych daty/czasu. Funkcje różnią się głównie tym, że zwracają wynik w innych typach danych.
| Funkcja | Składnia | Opis | Typ zwracany |
|---|---|---|---|
| DATEFROMPARTS | DATEFROMPARTS ( year, month, day ) | Zwraca wartość daty o typie date po podaniu argumentów w formie liczb dla roku, miesiąca i dnia | date |
| DATETIME2FROMPARTS | DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision ) | Zwraca wartość daty i czasu o typie danych datetime2 po podaniu argumentów w formie liczb dla roku, miesiąca i dnia, godziny, minuty, sekundy, frakcji i precyzji. Precyzja może być liczbą maksymalnie 7 cyfrową. | datetime2(precision ) |
| DATETIMEFROMPARTS | DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds ) | Zwraca datę po podaniu argumentów w formie liczb dla roku, miesiąca i dnia, godzinn, minut, sekund, milisekund. | datetime |
| DATETIMEOFFSETFROMPARTS | DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision ) | Zwraca wartość daty, czasu i strefę czasową po podaniu argumentów w formie liczb dla roku, miesiąca i dnia, godziny, minuty, sekundy, frakcji i precyzji. Precyzja może być liczbą maksymalnie 7 cyfrową. | datetime(precision ) |
| SMALLDATETIMEFROMPARTS | SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute ) | Zwaraca wartość daty i czasu w typie danych smalldatetime, a więc bez sekund. | smalldatetime |
| TIMEFROMPARTS | TIMEFROMPARTS ( hour, minute, seconds, fractions, precision ) | Zwraca czas po podaniu godziny, minuty, sekundy, frakcji i precyzji. Precyzja może być liczbą maksymalnie 7 cyfrową. | time(precision ) |
--tomaszkenig.pl select DATEFROMPARTS(2020,12,07) as f_DATEFROMPARTS, DATETIME2FROMPARTS(2020,12,07,12,17,53,1234,7) as f_DATETIME2FROMPARTS, DATETIMEFROMPARTS(2020,12,07,12,17,53,123) as f_DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS(2020,12,07,12,17,53,1234,1,30,7) as f_DATETIMEOFFSETFROMPARTS, SMALLDATETIMEFROMPARTS(2020,12,07,12,17) as f_SMALLDATETIMEFROMPARTS, TIMEFROMPARTS(12,17,53,1234,7) as f_TIMEFROMPARTS ;
4. Funkcje zwracające różnice pomiędzy datami
Jeśli chce policzyć różnice pomiędzy datami najprostszym sposobem jest działanie arytmetyczne odejmujące jedną datę od drugiej. Możesz też użyć w tym celu funkcji DATEDIFF I DATEDIFF_BIG. Za pomocą tych funkcji w prostszy sposób możesz określić w jakich jednostkach chcesz otrzymać wynik. Mogą to być dni, miesiące, lata, milisekundy itp. Określasz to w argumencie datepart. Funkcji DATEDIFF_BIG używaj, gdy spodziewasz się w wyniku większego niż zakres typu danych int.Funkcja DATEDIFF_BIG dostępna jest od wersji SQL Server 2016.
| Funkcja | Składnia | Opis | Typ zwracany |
|---|---|---|---|
| DATEDIFF | DATEDIFF ( datepart , startdate , enddate ) | Zwraca różnicę pomiędzy wskazanymi datami. To, czy różnica będzie podana w dniach, latach, czy sekundach określamy w argumencie datepart. Typ danych zwracany to int | int |
| DATEDIFF_BIG | DATEDIFF_BIG ( datepart, startdate , enddate ) | Zwraca różnicę pomiędzy wskazanymi datami. To, czy różnica będzie podana w dniach, latach, czy sekundach określamy w argumencie datepart. Typ danych zwracany to bigint. Używamy tej funkcji do obliczenia różnic w bardzo dużych przedziałach. Np. gdy różnicę pomiędzy dniami chcemy podać w milisekundach | bigint |
--tomaszkenig.pl select convert(int, convert(datetime, '2017-09-01') - convert(datetime, '2017-06-21')) as zwykla_roznica_dat, convert(float, convert(datetime, '2017-09-01 13:22:11') - convert(datetime, '2017-06-21 14:44:00')) * 86400 as zwykla_roznica_dat_w_sekundach, datediff(DD,'1906-01-01' ,getdate()) --w SQL Server 2016: select datediff_big(ms,'1906-01-01' ,getdate());
5. Funkcje zwracające bieżącą datę i czas
Kolejne funkcje daty i czasu w SQL Server to funkcje, za pomocą których możesz uzyskać z serwera datę i godzinę. Funkcje różnią się przede wszystkim zwracaną precyzją. Najczęściej będziesz używał funkcji GETDATE().
| FUNKCJA | Składnia | Opis | Typ zwracany |
|---|---|---|---|
| SYSDATETIME | SYSDATETIME () | Zwraca bieżącą datę i czas o typie danych datetime2 z komputera na którym działa SQL Server. Nie zwraca strefy czasowej. | datetime2(7) |
| SYSDATETIMEOFFSET | SYSDATETIMEOFFSET ( ) | Zwraca bieżącą datę i czas o typie danych datetimeoffset z komputera na którym działa SQL Server. Zwraca strefę czasową. | datetimeoffset(7) |
| SYSUTCDATETIME | SYSUTCDATETIME ( ) | Zwraca bieżącą datę i czas o typie danych datetime2 z komputera na którym działa SQL Server. Zwraca strefę czasową. | datetime2(7) |
| CURRENT_TIMESTAMP | CURRENT_TIMESTAMP | Zwraca bieżącą datę i czas z komputera, na którym działa serwer. | datetime |
| GETDATE | GETDATE ( ) | Zwraca bieżącą datę i czas z komputera, na którym działa serwer. | datetime |
| GETUTCDATE | GETUTCDATE ( ) | Zwraca datę i czas z komputera, na którym działa serwer w formie czasu UTC. | datetime |
--tomaszkenig.pl SELECT SYSDATETIME() AS F1, SYSDATETIMEOFFSET() AS F2, SYSUTCDATETIME() AS F3, CURRENT_TIMESTAMP AS F4, GETDATE() AS F5, GETUTCDATE() AS F6;
6. Funkcje manipulujące datą i czasem
Najważniejszą funkcją do manipulacji datą i czasem jest funkcja DATEADD(). Możesz za jej pomocą dodawać lub odejmować od daty dowolne jednostki czasu jak dni, miesiące, lata itp.
| Funkcja | Składnia | Opis | Typ zwracany |
|---|---|---|---|
| DATEADD | DATEADD (datepart, number , date ) | Funkcja dodaje do daty/czasu określony w argumencie interwał czasu. Możemy dodać dzień, miesiąc, rok, sekundy itp.. | typ danych z argumentu |
| EOMONTH | EOMONTH ( start_date [, month_to_add ] ) | Zwraca ostatni dzień miesiąca z podanej daty. W argumencie możemy też dodać lub odjąć miesiące. | typ danych z argumentu |
| SWITCHOFFSET | SWITCHOFFSET(DATETIMEOFFSET , time_zone) | Funkcja zmienia strefę czasową dla dat zapisanych w typie DATETIMEOFFSET. | DATETIMEOFFSET |
| TODATETIMEOFFSET | TODATETIMEOFFSET (expression , time_zone) | Zmienia daty o typie datetime2 na datetimeoffset. | DATETIMEOFFSET |
SELECT
DATEADD(DD, 15 , getdate()) AS F1,
DATEADD(HH, -15 , getdate()) AS F2,
EOMONTH(getdate()) AS F3,
EOMONTH(getdate(),-6) AS F4,
SWITCHOFFSET('2020-12-07 10:57:53.0001234 +00:01' , '+00:10') AS F5,
TODATETIMEOFFSET (GETDATE() , '+00:10')
;7. Funkcje logiczne i systemowe daty i czasu
| Funkcja | Składnia | Opis | Typ zwracany |
|---|---|---|---|
| ISDATE | ISDATE ( expression) | Funkcja sprawdza, czy podany argumentjest datą o typie datetime lub smalldatatime. Zwraca wartość 1 dla prawdy i 0 dla fałsz. Dla daty = NULL zwróci wartość 0. | int |
| @@DATEFIRST | @@DATEFIRST | Zwraca pierwszy dzień tygodnia ustawiony dla bieżącej sesji w formie liczb od 1 do 7. | tinyint |
| SET DATEFIRST | SET DATEFIRST { number | @number_var } | Ustawia pierwszy dzień tygodnia dla bieżącej sesji. Przyjmuje wartosci od 1 do 7. | |
| SET DATEFORMAT | SET DATEFORMAT { format | @format_var } | Funkcja ustawia porządek dnia, miesiąca i roku w wyświetlanej dacie. Daty o typie datetime i smalldatetime. |
select
ISDATE('2019-01-01') as f_1,
ISDATE('20190101') as f_2,
@@DATEFIRST as f_3
go
SET DATEFIRST 7
go
select @@DATEFIRST as f_4
goŹródło: Microsoft
3 komentarze “Funkcje daty i czasu w SQL Server”
nie
ja również nie
pozdrawiam
nie podoba mi się, nie rozwiązało mojego problemu