Podczas pracy z MS SQL często napotyka się problemy ze zmiennymi typu: date, datetime, smalldatetime, time.
Wynika to z faktu, że w różnych środowiskach możemy mieć ustawione inne formaty daty.
Inny problem to również to, jak pozbyć się np. minut i sekund z datetime lub jak skonwertować wartość na inny typ danych.
Oto kilka przykładów:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT CONVERT(DATE, GETDATE(), 120) SELECT CONVERT(SMALLDATETIME, GETDATE(), 120) SELECT CONVERT(DATETIME, GETDATE(), 120) SELECT CONVERT(DATETIME2(2), GETDATE(), 120) SELECT CONVERT(TIME, GETDATE(), 120) SELECT CONVERT(TIME(2), GETDATE(), 120) |
W powyższych przykładach polecenie CONVERT pozwala na określenie stylu konwersji daty 120, czyli yyyy-mm-dd hh:mi:ss (24h).
Innym sposobem by pozbawić daty godziny jest konwersja na string.
|
1 2 |
SELECT CONVERT(CHAR(10), GETDATE(), 120) |
1. Praca z datą i czasem w MS SQL – Użycie funkcji DATEPART
Funkcja DATEPART pozwala na wydobycie fragmentu daty, takiej jak np. rok, miesiąc czy dzień itd.
|
1 2 3 4 5 6 |
SELECT * FROM t_Date WHERE DATEPART(YEAR, fDATE) = '2011' AND DATEPART(MONTH, fDATE) = '12' AND DATEPART(DAY, fDATE) = '09' |
2. Praca z datą i czasem w MS SQL – Zmiana daty na liczbę (integer, float)
Czasami konieczna może być możliwość zamiany daty na liczbę, np. gdy chcemy ją eksportować lub program ma problem z rozumieniem daty.
|
1 2 3 4 |
SELECT YEAR(GETDATE()) * 100000 + MONTH(GETDATE()) * 1000 + DAY(GETDATE()) SELECT CAST(GETDATE() AS FLOAT) |
Pierwszy przykład za pomocą mnożenia i funkcji zmienia datę na typ integer.
W połączeniu z funkcją floor typ flout pozwala nam na pozbycie się minut i sekund.
|
1 2 |
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) |
3. Praca z datą i czasem w MS SQL – Odejmowanie i dodawanie dat
Do odejmowania dat używamy funkcji dateadd. By dodać wartość do daty podajemy dla funkcji wartość dodatnią by odjąć ujemną.
|
1 2 |
SELECT DATEADD(DAY, -1, GETDATE()) |
Celem odjęcia daty od daty, używamy funkcji datediff. Syntax: DATEDIFF(interwał, początek, koniec)
|
1 2 |
SELECT DATEDIFF(DAY, '01-01-1900', GETDATE()) |
4. Praca z datą i czasem w MS SQL – inny format daty
Tworząc raport np. w tabeli przestawnej, przydaje się niewystępujący w MS SQL format daty rrrrmm, a jest on niezbędny, gdy chcemy zaprezentować dane z kilku lat po miesiącach. Rozwiązanie:
Dla formatu yyyymm oraz yyyy-mm
|
1 2 3 4 5 |
SELECT YEAR(GETDATE()) * 100 + MONTH(GETDATE()) SELECT CONVERT(VARCHAR(5), YEAR(GETDATE())) + '-' + RIGHT('0' + CONVERT(VARCHAR(3), MONTH(GETDATE())), 2 |
5. Praca z datą i czasem w MS SQL – ustawianie formatu daty (SET DATEFORMAT)
Poniżej przykład jak przy użyciu funkcji SET DATEFORMAT ustawić format daty dla zmiennych w MS SQL.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SET DATEFORMAT dmy; GO DECLARE @datevar datetime2 = '31/12/2011 10:01:01.1234567'; SELECT @datevar; GO SET DATEFORMAT mdy; GO DECLARE @datevar datetime2 = '12/31/2011 10:01:01.1234567'; SELECT @datevar; |
EDIT:
datetime2 to nic innego jak rozwinięcie typu datetime, które zwiększa możliwości oraz precyzję przechowywania danych dot. zapisu daty i godziny.
Microsoft SQL Server starszy niż wersja 2008 (Katmai, 10.0) oferował dotychczas jedynie typy datetime oraz smalldatetime, które z kolei poprzez to, iż oferowały w swoim formacie zapis dot. daty i godziny utrudniał pracę jedynie z samą datą lub godziną.
Ponadto typ jakim jest datetime oferuje znacznie mniejszy zakres danych ograniczony do roku wprowadzenia kalendarza gregoraińskiego, który miał miejsce w Anglii, w roku 1753 (01/01/1753 – 31/12/9999).
datetime2 zaś jest w stanie przechować dane dotyczące dat z zakresu 01/01/0001 – 31/12/9999.
Jeśli mowa o precyzji i rozmiarze dla tych dwóch typów wynoszą one odpowiednio:
datetime : 0,00333 sekund oraz 8 bajtów
datetime2 : 100 nanosekund oraz 6-8 bajtów
Bardzo ciekawy i pomocny artykuł. Warto dodać od kiedy zmienna datetime2 pojawila się w mssql.
Wiktor oby tak dalej. Dobra robota.