Jakie formaty literalne daty i godziny są bezpieczne w języku LANGUAGE i DATEFORMAT?

Łatwo jest wykazać, że wiele formatów daty / godziny innych niż dwa następujące jest podatnych na błędną interpretację z powodu ustawienia języka, ustawienia daty lub domyślnego języka logowania:

yyyyMMdd                 -- unseparated, date only
yyyy-MM-ddThh:mm:ss.fff  -- date dash separated, date/time separated by T 

Nawet ten format, bez T, może wyglądać na prawidłowy format ISO 8601, ale nie działa w kilku językach:

DECLARE @d varchar(32) = '2017-03-13 23:22:21.020';

SET LANGUAGE Deutsch;
SELECT CONVERT(datetime, @d);

SET LANGUAGE Français;
SELECT CONVERT(datetime, @d);

Wyniki:

Die Spracheneinstellung wurde auf Deutsch geändert.

Msg 242, poziom 16, stan 3
Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.

Le paramètre de langue est passé à Français.

Msg 242, poziom 16, stan 3
La konwersji d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Nie udaje im się to tak, jak gdyby w języku angielskim dokonałem transpozycji miesiąca i dnia, aby sformułować składnik daty yyyy-dd-mm:

DECLARE @d varchar(32) = '2017-13-03 23:22:21.020';

SET LANGUAGE us_english;
SELECT CONVERT(datetime, @d);

Wynik:

Msg 242, poziom 16, stan 3
Konwersja typu danych varchar na typ danych data-godzina spowodowała, że ​​wartość była poza zakresem.

(To nie jest Microsoft Access, który jest dla ciebie „miły” i naprawia dla ciebie transpozycję. W niektórych przypadkach mogą wystąpić podobne błędy SET DATEFORMAT ydm;- to nie jest tylko kwestia języka, to tylko bardziej powszechny scenariusz, w którym zdarzają się awarie - i nie zawsze są zauważane, ponieważ czasami nie są błędami, po prostu 7 sierpnia stał się 8 lipca i nikt tego nie zauważył.)

Tak więc pytanie:

Teraz, gdy wiem, że istnieje kilka niebezpiecznych formatów, czy są jakieś inne formaty, które będą bezpieczne, biorąc pod uwagę dowolną kombinację języka i daty?

W dokumentacji wyraźnie zaznaczono, że jedynymi bezpiecznymi formatami są te, które zademonstrowałem na samym początku pytania:

yyyyMMdd                 -- unseparated, date only
yyyy-MM-ddThh:mm:ss.fff  -- date dash separated, date/time separated by T 

Jednak niedawno zwrócono mi uwagę, że istnieje trzeci format, który jest równie odporny na wszelkie ustawienia języka lub formatu daty:

yyyyMMdd hh:mm:ss.fff    -- unseparated date, no T separator

TL; DR: To prawda. Dla datetimei smalldatetime.

Czytaj dalej, aby uzyskać dłuższą wersję i tyle dowodów, ile dostaniesz.

Istnieje luka, która to wyjaśnia - podczas gdy główna treść tekstu nie uznaje się yyyyMMdd hh:...za format bezpieczny przed transponowanym językiem lub interpretacjami formatu daty, istnieje mały napis, który mówi, że część daty takiego ciągu nie jest sprawdzana w zależności od ustawień formatu daty :

Zwykle raczej nie podoba mi się tylko przyjmowanie dokumentacji za słowo. Możesz powiedzieć, że jestem trochę sceptyczny. Język też jest tu niejednoznaczny - stwierdza tylko, że chodzi o połączenie daty i godziny, a nie wyraźne wzywanie miejsca (to wszystko, co wiem, może być znakiem powrotu karetki). Mówi także, że nie jest on wielojęzyczny, co oznacza, że ​​może zawieść w niektórych językach, ale wkrótce dowiemy się, że to również jest niepoprawne.

Dlatego postanowiłem udowodnić, że żadna kombinacja języka / formatu daty nie może spowodować niepowodzenia tego konkretnego formatu.

Najpierw stworzyłem mały blok dynamicznego SQL dla każdego języka:

EXEC sys.sp_executesql @sql, N'@lang sysname', N'us_english';

To dało 34 wiersze wyników takich jak to:

EXEC sys.sp_executesql @sql, N'@lang sysname', N'us_english';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Deutsch';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Français';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'日本語';
...
EXEC sys.sp_executesql @sql, N'@lang sysname', N'简体中文';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Arabic';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'ไทย';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'norsk (bokmål)';    

Skopiowałem ten wynik do nowego okna zapytania, a nad nim wygenerowałem ten kod, który, mam nadzieję, spróbuje przekonwertować tę samą datę (13 marca) na trzeci dzień 13 miesiąca w co najmniej jednym przypadku:

DECLARE @sql nvarchar(max) = N'
SET LANGUAGE @lang;
SET DATEFORMAT ydm;
SELECT @@LANGUAGE, CONVERT(datetime, ''20170313 23:22:21.020'');';

Nie, każdy język, w którym tylko się znalazło, działał ydm. Próbowałem też każdego innego formatu, a także każdego typu danych data / czas. 34 udane konwersje do 13 marca za każdym razem.

Przyznaję więc @AndriyM i @ErikE, że rzeczywiście istnieje trzeci bezpieczny format. Będę o tym pamiętać w przyszłych postach, ale waliłem w bębny o pozostałe dwa w tak wielu miejscach, że nie zamierzam ich teraz wyłapywać i poprawiać.

Zasadniczo wydaje ci się, że ten będzie bezpieczny, ale nie:

yyyyMMddThh:mm:ss.fff    -- unseparated date, T separator

Myślę, że w każdym języku da to równowartość:

Wiadomość 241, poziom 16, stan 1, wiersz 8
Konwersja nie powiodła się podczas konwersji daty i / lub godziny z ciągu znaków.

Dla kompletności, jest czwartą bezpieczny formatu, ale jest bezpieczny tylko dla konwersji do nowszych typów data / czas ( date, datetime2, datetimeoffset). W takich przypadkach ustawienia języka nie mogą zakłócać:

yyyy-MM-dd hh:mm:...

Jednak zdecydowanie odradzam jego użycie, ponieważ działa tylko dla nowszych typów, a stare są nadal bardzo intensywne , z mojego doświadczenia. Dlaczego myślniki są tam, skoro wszędzie indziej (lub w rzeczywistości w tym samym kodzie, jeśli zmienia się typ danych), trzeba je usunąć?

SET LANGUAGE Deutsch;
DECLARE @dashes char(10) = '2017-03-07 03:34';
DECLARE @d date = @dashes, @dt datetime = @dashes, @dt2 datetime2 = @dashes;

SELECT DATENAME(MONTH,@d), DATENAME(MONTH,@dt), DATENAME(MONTH,@dt2);

Nawet biorąc pod uwagę ten sam ciąg źródłowy, konwersje dały całkiem inne wyniki:

März    Juli    März

Format, który działa na datetime ( yyyyMMdd) będzie również zawsze aktualne i praca dla innych nowych typów. IMHO, po prostu zawsze tego używaj. Biorąc pod uwagę trzeci format typów z datą / godziną ( yyyyMMdd hh:...), pozwoli to zachować większą spójność - nawet jeśli składnik daty jest zawsze nieco mniej czytelny.

Teraz zajmie mi tylko kilka lat, daję lub biorę, aby przyzwyczaić się do demonstrowania trzech bezpiecznych formatów, kiedy mówię o ciągach reprezentujących daty.