Wstawianie daty i czasu w SQL Server
W bazach danych kolumny przechowujące dane typu data i czas pełnią nierzadko bardzo ważne role. Zrozumienie, w jaki sposób należy wstawiać dane tego typu, zapobiega sytuacjom, w których w bazie danych znajdują się niespójne logicznie dane. Niniejszy artykuł ma pomóc użytkownikom systemów SQL Server 2000/2005 uniknąć błędów związanych z zapisywaniem danych typu datetime lub smalldatetime do baz danych.
Zawartość strony
Wstęp
W systemie SQL Server 2000/2005 wstawianie daty i czasu do tabel lub zmiennych odbywa się przeważnie bądź przez podanie przez użytkownika danych czasowych w formie napisu (danych typu varchar), lub poprzez wykorzystanie funkcji generujących datę i czas (takich jak GETDATE). O ile w przypadku użycia funkcji właściwie nie ma mowy o jakichkolwiek niejednoznaczościach danych, o tyle w momencie, gdy użytkownik samodzielnie podaje ciąg znaków w sposób jawny, SQL Server dokonuje niejawnej konwersji podanego napisu do odpowiedniego formatu docelowego – datetime lub smalldatetime.
Artykuł ma na celu uzmysłowienie użytkownikom systemu SQL Server, w jaki sposób należy podawać datę i czas w formacie tekstowym, by zawsze były zapisywane właściwie (zgodnie z intencją użytkownika).
Autor, zamiast wstawiania wierszy w przykładach umieszczonych w artykule, posłuży się konwersją jawną (przy użyciu funkcji rzutującej CAST) do odpowiedniego typu danych.
Typy danych datetime i smalldatetime
Jak dotąd żadna wersja systemu SQL Server nie udostępnia typów danych do przechowywania osobno daty, a osobno czasu. Użytkownik może wykorzystać dwa formaty zawierające jednocześnie i datę, i czas: datetime oraz smalldatetime.
Datetime jest typem danych, który potrafi przechowywać daty od 1 stycznia 1753 roku do 31 grudnia 9999 roku z dokładnością do 3 i 1/3 milisekundy. SQL Server 2000/2005 przechowuje dane tego typu jako dwie 4-bajtowe liczby całkowite. Pierwsza liczba przechowuje datę - ilość dni przed lub po 1 stycznia 1900 roku, zaś druga przechowuje czas – liczbę jednostek (1/300 sekundy) po północy.
Smalldatetime jest z kolei typem danych, w którym można przechowywać daty od 1 stycznia 1900 roku do 6 czerwca 2079 roku z dokładnością do 1 minuty. SQL Server 2000/2005 przechowuje dane tego typu jako dwie 2-bajtowe liczby całkowite. Pierwsza liczba przechowuje datę - ilość dni po 1 stycznia 1900 roku, zaś druga przechowuje czas – liczbę minut po północy.
Próba wstawienia daty spoza zakresu danego typu kończy się zwróceniem komunikatu o wystąpieniu błędu o numerze 242:
Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted _ in an out-of-range datetime value.
Warto też zwrócić uwagę na dokładność obydwu wymienionych typów danych. Typ datetime ma dokładność do 3 i 1/3 milisekundy. Oznacza to, że ostatnią cyfrą (oznaczającą jednostki milisekund) jest zawsze jedna z trzech cyfr: 0, 3 lub 7. Podanie jako wartości dla typu datetime napisu z inną cyfrą na ostatniej pozycji wiąże się z zaokrągleniem daty i czasu tak, by na ostatniej pozycji stała jedna z trzech wymienionych cyfr. Przykład:
SELECT
CAST('20070205 23:59:59.998' AS datetime), -- 2007-02-05 23:59:59.997
CAST('20070205 23:59:59.999' AS datetime) -- 2007-02-06 00:00:00.000
Powyższy przykład obrazuje, jak SQL Server zaokrągla dane podawane do kolumny / zmiennej typu datetime. Zauważmy, że drugi napis został zrzutowany na typ datetime z zaokrągleniem, które sprawiło, że data nie jest taka, jaką zapewne chciałby wstawić użytkownik (wstawiona data to dzień następny w odniesieniu do daty podawanej przez użytkownika).
Kolejny przykład, tym razem dotyczący typu smalldatetime:
SELECT
CAST ('20070205 23:29:29.998' AS smalldatetime), -- 2007-02-05 23:29:00
CAST ('20070205 23:29:29.999' AS smalldatetime) -- 2007-02-05 23:30:00
Powyższy przykład obrazuje, jak SQL Server zaokrągla dane podawane do kolumny / zmiennej typu smalldatetime. Zauważmy, że jeżeli w części obrazującej czas podano 29.998 sekundy (lub mniej), to zaokrąglenie czasu następuje w dół do najbliższej minuty, zaś przy końcówkach 29.999 (lub więcej) zaokrąglenie następuje w górę również do najbliższej minuty. Oznacza to, że prawdopodobnie konwersja odbywa się w dwóch krokach – najpierw napis jest konwertowany do typu datetime, a dopiero w dalszej kolejności do typu smalldatetime.
Ustawienia wpływające na wstawianie daty i czasu
Opcja „two digit year cutoff”
Wstawianie daty i czasu do kolumn lub zmiennych typu datetime lub smalldatetime jest uwarunkowane pewnymi ustawieniami. Pierwszym ustawieniem, które ma wpływ na to, w jaki sposób SQL Server interpretuje podawane jako data i czas napisy, jest opcja serwera two digit year cutoff. Opcja ta może przyjmować jako wartość liczbę całkowitą z zakresu od 1753 do 9999. Służy ona do zdefiniowania sposobu, w jaki SQL Server interpretuje dwucyfrowy skrót roku. Domyślną wartością tej opcji jest 2049, co oznacza, że dwucyfrowy skrót 49 jest traktowany jako rok 2049, zaś dwucyfrowy skrót 50 jest interpretowany jako rok 1950.
Sposób ustawienia opcji two digit year cutoff przedstawia poniższy fragment kodu:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'two digit year cutoff', 2030
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
-- Test nowych ustawien
SELECT CAST('310101' AS datetime) -- 1931-01-01 00:00:00.000
| Informacja: |
Obiekty OLE Automation używają dla opcji two digit year cutoff domyślnej wartości 2030. Aby zachować spójność i jednoznaczność dwucyfrowych skrótów roku między systemem SQL Server a obiektami OLE Automation, możesz zmienić ustawienie opcji w instancji SQL Server. Firma Microsoft, podobnie jak autor artykułu, zaleca jednak podawanie roku zawsze w postaci czterech cyfr. Pozwala to uniknąć niejednoznaczności i uniezależnić wstawianie daty i czasu od opisywanej powyżej opcji serwera. |
Ustawienia językowe
Kolejną opcją, która nie pozostaje bez wpływu na sposób, w jaki SQL Server interpretuje wstawiane przez użytkowników dane typu data i czas, jest ustawienie języka na poziomie sesji. Język można ustawić na dwa sposoby: określając go dla wybranego logina (opcja Default language – patrz rysunek poniżej) lub ustawiając go w sposób jawny na poziomie sesji.
Rys. 1 Właściwość Default language logina
Ustawiając odpowiedni język domyślny dla logina powodujesz, że językiem obowiązującym w każdej sesji logina będzie język wybrany jako domyślny. Tworząc login przy użyciu polecenia CREATE LOGIN w SQL Server 2005, należy użyć opcji DEFAULT_LANGUAGE. Przykład:
USE [master] GO CREATE LOGIN [WARSAW\TechNetLogin] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[polski] GO
Jeżeli chcesz sprawdzić, jaki domyślny język został przypisany do każdego z loginów, możesz użyć na przykład poniższego zapytania:
SELECT name, type_desc, default_language_name FROM sys.server_principals WHERE type_desc IN (N'SQL_LOGIN',N'WINDOWS_GROUP',N'WINDOWS_LOGIN')
Na poziomie sesji można zmienić ustawienia języka używając następującej składni:
SET LANGUAGE polski
Ustawiona w ten sposób opcja językowa przesłania na poziomie sesji domyślne ustawienia dla logina.
Nazwę i identyfikator aktualnie ustawionego na sesji języka można uzyskać wykorzystując funkcje systemowe @@LANGUAGE i @@LANGID:
SELECT @@LANGUAGE, @@LANGID
SQL Server 2005 obsługuje 33 języki (domyślnym i zapewne najczęściej używanym językiem jest us_english). Pełną listę języków można uzyskać wykorzystując systemową procedurę składowaną sp_helplanguage lub widok systemowy sys.syslanguages, czyli wykonując jeden z dwóch poniższych fragmentów kodu T-SQL:
EXEC sp_helplanguage
lub
SELECT * FROM sys.syslanguages
Z tabeli, jaka jest zwracana jako wynik wykonania powyżych składni można pobrać nazwy obsługiwanych języków dla polecenia SET LANGUAGE (można użyć zarówno nazw języków, jak i odpowiadających im aliasów).
Za co odpowiada ustawienie językowe? Za wiele rzeczy (w nawiasach kolumny zwracane z procedury sp_helplanguage oraz widoku sys.syslanguages, które opisują wybrane aspekty ustawień językowych):
| • | określa pełne i skrócone nazwy miesięcy i nazwy dni tygodnia (months, shortmonths i days), |
| • | określa pierwszy dzień tygodnia (datefirst), |
| • | określa sposób interpretacji dat podawanych przez użytkownika (dateformat), |
| • | definiuje język komunikatów błędów dla użytkownika. |
| Informacja: |
Ustawienia językowe nie zmieniają się, jeżeli użyjesz składni EXECUTE AS LOGIN do zasymulowania aktywności innego logina. Na sesji obowiązują ustawienia językowe logina, który otworzył sesję (o ile nie zostały one nadpisane przez składnię SET LANGUAGE). |
Ustawienie DATEFORMAT
Jednym z ustawień, jakie wprowadza język obowiązujący na poziomie sesji jest DATEFORMAT. Jest to opcja odpowiadająca za sposób, w jaki SQL Server interpretuje niektóre formaty dat. Opcja ta przyjmuje jedną z następujących wartości: mdy, dmy, ymd, ydm, myd, dym. Dla przykładu, domyślnym ustawieniem dla języka us_english jest mdy, co oznacza, że w podawanych datach w przypadku jakichkolwiek niejednoznaczności SQL Server założy, że użytkownik w pierwszej kolejności podaje miesiąc (m – month), następnie dzień (d – day), a na koniec rok (y – year). O tym, kiedy SQL Server stosuje się do obowiązującego ustawienia DATEFORMAT traktuje dalsza część artukułu.
Na poziomie sesji można zmienić ustawienia opcji DATEFORMAT używając składni SET DATEFORMAT:
SET DATEFORMAT ymd
Ustawiona w ten sposób opcja DATEFORMAT przesłania na poziomie sesji ustawienia dla obowiązującego na poziomie sesji języka.
Ustawienie DATEFIRST
Kolejnym ustawieniem, jakie wprowadza język obowiązujący na poziomie sesji jest DATEFIRST. Jest to opcja odpowiadająca za to, który dzień tygodnia (poniedziałek, wtorek,...) będzie pierwszym dniem tygodnia. Dni tygodnia są numerowane od 1 (1 – poniedziałek,...,7- niedziela), a zatem ustawienie opcji DATEFIRST na 7 oznacza, że niedziela jest pierwszym dniem tygodnia.
Na poziomie sesji można zmienić ustawienia opcji DATEFIRST używając składni SET DATEFIRST (poniższy kod ustawia poniedziałek jako pierwszy dzień tygodnia):
SET DATEFIRST 1
Aby sprawdzić ustawienia opcji DATEFIRST na poziomie sesji możesz użyć funkcji systemowej @@DATEFIRST:
SELECT @@DATEFIRST
| Informacja: |
O ile opcja DATEFIRST nie wpływa na interpretację daty i czasu przez SQL Server, ma znaczenie przy dokonywaniu obliczeń, w których obliczana jest różnica między datami wyrażona w tygodniach. Warto pamiętać, że funkcja DATEDIFF licząca różnicę między dwiema datami nie uwzględnia opcji DATEFIRST i działa tak, jakby niedziela była zawsze pierwszym dniem tygodnia (jest to następstwo tego, że funkcja ta została zaprojektowana jako deterministyczna). |
Formaty daty i czasu
Poniższy fragment kodu przedstawia kilka możliwych formatów wstawiania daty i czasu po przełączeniu języka sesji na język polski:
SET LANGUAGE polski
SELECT CAST('1 luty 2003' AS datetime) -- zależy od języka (miesiąc)
SELECT CAST('1 II 2003' AS datetime) -- zależy od języka (skrót miesiąca)
SELECT CAST('01-02-03' AS datetime) -- zależy od języka (dateformat)
SELECT CAST('01/02/03' AS datetime) -- zależy od języka (dateformat)
SELECT CAST('01.02.03' AS datetime) -- zależy od języka (dateformat)
SELECT CAST('01-02-03' AS datetime) -- zależy od języka (dateformat)
SELECT CAST('2003-02-01' AS datetime) -- UWAGA !!! zależy od dateformat!!!
SELECT CAST('2003/02/01' AS datetime) -- UWAGA !!! zależy od dateformat!!!
SELECT CAST('2003.02.01' AS datetime) -- UWAGA !!! zależy od dateformat!!!
SELECT CAST('20030201' AS datetime) -- nie zależy od języka
SELECT CAST('030201' AS datetime) -- nie zależy od języka
SELECT CAST('2003-02-01T00:00:00.000' AS datetime) -- nie zależy od języka
SELECT { d '2003-02-01' } -- nie zależy od języka
SELECT { ts '2003-02-01 00:00:00.000' } -- nie zależy od języka
SELECT CAST('10:20:30' AS datetime) -- 1900-01-01 10:20:30.000
SELECT CAST ('10:20' AS datetime) -- 1900-01-01 10:20:00.000
SELECT CAST ('10:20:30:400' AS datetime) -- 1900-01-01 10:20:30.400
SELECT CAST ('10:20:30.4' AS datetime) -- 1900-01-01 10:20:30.400
SELECT CAST ('1PM' AS datetime) -- 1900-01-01 13:00:00.000
SELECT CAST ('1 PM' AS datetime) -- 1900-01-01 13:00:00.000
SELECT CAST ('01:20:30:400AM' AS datetime) -- 1900-01-01 01:20:30.400
SELECT { t '10:20:30' } -- dzisiejsza data!!!
Z wyników zwróconych przez powyższy fragment kodu i z lektury Books Online można wyciągnąć następujące wnioski:
1. | Istnieje bardzo wiele możliwych poprawnych formatów wstawiania daty i czasu (oczywiście autor nie wymienił wszystkich możliwych formatów). |
2. | Możliwe separatory fragmentów daty to: myślnik (-), ukośnik (/) oraz kropka (.). |
3. | Ustawienie DATEFORMAT ma duży wpływ na interpretowanie daty przez SQL Server. Użyty w powyższym przykładzie język polski ma ustawienie opcji DATEFORMAT na dmy. Powoduje to, że SQL Server w pierwszej kolejności w ciągu znaków oznaczających datę szuka czterech cyfr roku (tak się dzieje niezależnie od ustawienia DATEFORMAT, a zatem niezależnie od języka), a następnie (zgodnie z formatem dmy) wybiera pierwszą napotkaną parę cyfr jako dzień miesiąca (d) i następną parę jako numer miesiąca (m). Jeżeli nie zostanie znaleziony rok wyrażony czterema cyframi, dodatkowo SQL Server wybiera po dwie cyfry interpretując każdą parę cyfr kolejno zgodnie z opcją DATEFORMAT (dla polskiego języka – pierwsze dwie cyfry to dzień, druga para cyfr to miesiąc, a trzecia to rok). |
4. | Ciągi znaków bez separatorów między rokiem, miesiącem i dniem miesiąca są interpretowane przez SQL Server zawsze tak samo, niezależnie od ustawień językowych i opcji DATEFORMAT (przy czym lepszym wyborem jest format RRRRMMDD zamiast RRMMDD z uwagi na opisaną w artykule opcję two digit year cutoff). |
5. | Format ISO 8601 (np. '2003-02-01T00:00:00.000') jest formatem daty i czasu interpretowanym niezależnie od języka. |
6. | Formaty preferowane dla ADO, ODBC i OLEDB (formaty używające nawiasów klamrowych, czyli postaci kanoniczne ODBC daty i czasu, samej daty lub samego czasu), np. { ts '2003-02-01 00:00:00.000' } są interpretowane niezależnie od języka i opcji DATEFORMAT. |
7. | Czas można podawać z wybraną przez siebie dokładnością. Akceptowany jest angielski format czasu (AM / PM). |
Podsumowanie
Niniejszy artykuł ma na celu pomóc użytkownikom systemu SQL Server 2000/2005 wybrać optymalny format dla wstawianych danych typu data i czas. Wnioski nasuwają się same:
1. | Należy pamiętać o zakresach i dokładności typów datetime i smalldatetime. |
2. | Należy uwzględniać opcje wpływające na interpretowanie przez system podawanych przez użytkowników danych typu data i czas. |
3. | Rok należy podawać w postaci czterech cyfr. |
4. | Najlepszym formatem daty dla aplikacji używających ADO, ODBC lub OLEDB jest postać kanoniczna ODBC użyta w notacji z nawiasami klamrowymi. |
5. | Najlepszym formatem daty dla aplikacji, które używają innych API niż ADO, ODBC lub OLEDB jest postać RRRRMMDD bez używania żadnego znaku podziału daty na rok, miesiąc i dzień. |
Poprzez stosowanie się do tych punktów można uniknąć problemów związanych z niejednoznacznością danych typu data i czas.
Kiedy w SQL Server znajdą się nowe typy danych, dedykowane do przechowywania osobno daty i czasu? Kto wie... . Może już w następnej odsłonie systemu (nazwa kodowa Katmai), której już teraz wyglądam z niecierpliwością, a której wersja finalna może ujrzeć światło dzienne już w przyszłym roku!
Dodatek A – Interpretacja przez SQL Server wybranych formatów daty w odniesieniu do ustawień językowych
Wynikiem wykonania poniższego skryptu jest tabela, która pokazuje, jak SQL Server interpretuje datę podawaną przez użytkownika w wybranych formatach (w prosty sposób można dodać więcej formatów do poniższego testu, by sprawdzić, jak wybrany przez użytkownika format będzie interpretowany przez system zależnie od ustawień językowych).
DECLARE @language_default sysname
SET @language_default = @@LANGUAGE
DECLARE @i int
DECLARE @t table (
[LangID] int,
[Language] sysname,
[DateFormat] nchar(3),
[2001-02-03] datetime,
[20010203] datetime,
[010203] datetime,
[2001/02/03] datetime,
[2001.02.03] datetime,
[01/02/03] datetime,
[01-02-03] datetime,
[01.02.03] datetime)
DECLARE @language sysname
SET @i = 0
WHILE @i <= 32
BEGIN
SELECT @language = [name] FROM sys.syslanguages WHERE [langid] = @i
SET LANGUAGE @language
INSERT INTO @t
SELECT
[langid],[alias], [dateformat],
CAST('2001-02-03' AS datetime),
CAST('20010203' AS datetime),
CAST('010203' AS datetime),
CAST('2001/02/03' AS datetime),
CAST('2001.02.03' AS datetime),
CAST('01/02/03' AS datetime),
CAST('01-02-03' AS datetime),
CAST('01.02.03' AS datetime)
FROM sys.syslanguages
WHERE [langid] = @@LANGID
SET @i = @i + 1
END
SELECT * FROM @t
SET LANGUAGE @language_default
 | Paweł Potasiński (Microsoft Certified Trainer, Asseco Business Solutions S.A.) |