[2005] Datumsformat yyyy-mm-dd wird als falsch bezeichnet

Question

Hallo, ich bin dabei, eine Beispieldatenbank mit einem Skript zu erstellen (mit SSMSEE). Das klappt weitgehend, aber das Datumsformat '1980-02-16' wird nicht akzeptiert:

[quote]Bei der Konvertierung eines char-Datentyps in einen datetime-Datentyp liegt der datetime-Wert außerhalb des gültigen Bereichs.[/quote]

Das verstehe ich nicht, denn auf http://msdn.microsoft.com/de-de/library/ms180878.aspx#StringLiteralDateandTimeFormats heißt es ausdrücklich, dass das Datumsformat 'ISO 8601-Numerisch' DateFormat-unabhängig ist und deshalb immer richtig erkannt werden müsste (02 als Monat, 16 als Tag, nicht umgekehrt).

Das Skript ist für eine Online-Einführung in SQL gedacht. Ich möchte deshalb weder ein spezielles Datumsformat noch CONVERT verwenden. Was ist da los, und was kann ich machen? Danke! Jürgen

PS. Ich arbeite normalerweise mit Firebird, aber ein paar Grundkenntnisse habe ich mir auch zu anderen DBMS angeeignet. Deshalb weiß ich, dass das Datumsformat problematisch sein kann; aber ISO8601 sollte doch universell gültig sein.

Answer 1

Hallo Juetho,

wie sieht Dein T-SQL Script den konkret aus, könntest Du das mal posten?

Also ich kann den Datumswert problemlos wandeln lassen. Man sollte bei der Convert Funktion besser immer explizit per Parameter angeben, welches Format vorliegt; für ODBC ist es der Wert 120 (Iso = 112). Für das ODBC Format gibt es mit {d ..} auch eine spezielle Notation; für {ts ...} ist für Timestamp also inklusive Uhrzeit.

-- ODBC

SELECT CONVERT(datetime, '1980-02-16')

-- ISO

SELECT CONVERT(datetime, '19800216')

-- Format 120 = ODBC kanonisch

SELECT CONVERT(datetime, '1980-02-16', 120)

-- ODBC Format

SELECT {d N'1980-02-16'}

 

SELECT {ts N'1980-02-16 14:24:00.00'}

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 2

CREATE TABLE Versicherungsnehmer
  (ID       INTEGER    NOT NULL IDENTITY(1,1),
   Name      VARCHAR(30)  NOT NULL,
   Vorname     VARCHAR(30),
   Geburtsdatum  DATETIME,
   Fuehrerschein  DATETIME,
   Ort       VARCHAR(30)  NOT NULL,
   PLZ       CHAR(5)    NOT NULL,
   Strasse     VARCHAR(30)  NOT NULL,
   Hausnummer   VARCHAR(10)  NOT NULL,
   Eigener_Kunde  CHAR(1)    NOT NULL,
   Versicherungsgesellschaft_ID INTEGER,
   CONSTRAINT Versicherungsnehmer_PK PRIMARY KEY (ID)
  );
GO
INSERT INTO MITARBEITER (PERSONALNUMMER, NAME, VORNAME, GEBURTSDATUM, TELEFON, MOBIL, EMAIL, RAUM, IST_LEITER, ABTEILUNG_ID)
 VALUES ('10002', 'Schneider', 'Daniela', '1980-02-16', '0231/5554988', NULL, '[e-mail]', '113', 'N', 1);
GO

Wie gesagt: auf CONVERT wollte ich verzichten. DATETIME (statt DATE) habe ich gewählt, damit es auch mit der 2005-EE läuft.

Answer 3

Das CREATE bezieht sich auf eine andere Tabelle as das INSERT. Aber egal, ich kann es nachvollziehen, es liegt wohl an den User Settings. Stelle ich bei mir mit SET DATEFORMAT, wie unten im Script, ein anderes Format ein, kommt der gleiche Fehler. Welche Sprache ist beim User, mit dem Du Dich anmeldest eingestellt?

Wieso willst Du auf CONVERT verzichten, es ist völlig normal? Unter Oracle verwendet man to_date, unter MS SQL Server eben Convert.
Ansonsten verwendet diese Schreibweise: {d N'1980-02-16'}

USE [AdventureWorks];

GO

 

CREATE TABLE MITARBEITER

     (Geburtsdatum datetime);

GO

 

-- Geht nicht

SET DATEFORMAT dmy;

INSERT INTO MITARBEITER (Geburtsdatum)

VALUES ('1980-02-16');

GO

 

-- Geht

SET DATEFORMAT ymd;

INSERT INTO MITARBEITER (Geburtsdatum)

VALUES ('1980-02-16');

GO

 

-- Geht

SET DATEFORMAT dmy;

INSERT INTO MITARBEITER (Geburtsdatum)

VALUES ({d N'1980-02-16'});

GO

 

SELECT *

FROM MITARBEITER;

 

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 4

Peinlich, ein Skript mit etwas mehr als 10 Tabellen, und ich hole genau eine falsche...

SET DATEFORMAT hat zwar meine Frage nicht beantwortet, warum ISO 8601 nicht akzeptiert wird; aber es war eine einfache Lösung .

Eine richtige Einstellung habe ich nicht gefunden. Das 2005-EE-Studio selbst hat nur eine englische Oberfläche; für den Standardbenutzer ist "deutsch" als Standard eingestellt. Aber dmy sollte doch unabhängig von DATEFORMAT passen. Nun ja...

Das Skript sollte für möglichst viele SQL-Dialekte möglichst identisch sein. (Es geht um eine Einführung in SQL, nicht in MS-SQL.)  Deshalb habe ich ISO 8601 verwendet und will auf Abweichungen weitgehend verzichten, auch wenn ich das natürlich nicht durchhalten kann. Für Nutzer von MS-SQL ist CONVERT natürlich ein sehr flexibles Hilfsmittel, aber z.B. ein Firebird-Nutzer kann damit gar nichts anfangen, und bei MySQL macht Convert etwas ganz anderes...

Danke jedenfalls für die prompte Hilfe! Jürgen

Answer 5

Hallo,

um die Frage zu beantworten, warum das nicht als ISO 8601 verstanden wird:

Der SQL Server versteht (zumindest bis SQL Server 2008) nur einen Teil der ISO 8601
(was eine üppige Anzahl von Formaten kennt).
Mit SQL Server 2008 und Date, DateTime2 haben sich die Regeln etwas gewandelt,
dort wird auch das explizite ISO-Format (mit 'T' spezifierer) generell erkannt.

Nur das Datumsformat 112 wird als ISO Datum erkannt, siehe CAST/CONVERT Stile -
heute (ab 2008) bezeichnet auch als Unstrukturierte Zeichenfolgen.

Alle anderen numerischen Formate, die Trennzeichen beinhalten, werden anhand der Regeln
für SET DATEFORMAT zerlegt - dass Trennzeichen (., :, -) spielt wiederum keine Rolle.
Die Festleung des Formates erfolgt indirekt auch durch die Sitzungssprache, siehe SET LANGUAGE ,
die wiederum bei der Anmeldung festgelegt wird (siehe CREATE LOGIN - DEFAULT_LANGUAGE).

Um eine Zeichenkette im Sinne der ISO zu markieren, müsstest Du mindestens die
ODBC Escape-Sequenzen für Datumsangaben verweden - da sie im SQL Server Parser
integriert sind, funktionieren sie auch ohne ODBC (also auch via OLE DB, .NET usw.).

Oder aber explizit den CONVERT-Stil vorgeben, in dem die Zeichenkette formatiert ist.

 

SET DATEFORMAT dmy	-- wir sprechen deutsch
GO
-- Geht nicht
SELECT CAST('1980-02-16' AS datetime)
GO

-- ODBC
SELECT 'ODBC Date', CAST({d '1980-02-16'} AS datetime)
SELECT 'ODBC Timestamp', CAST({ts '1980-02-16 15:30:45'} AS datetime)
GO
-- ISO ("unstrukturiertes Format")
SELECT 'ISO 112', CAST('19800216' AS datetime)
SELECT 'ISO 112 + Zeit', CAST('19800216 15:30:45' AS datetime)	
GO
-- Erst SQL Server 2008++
SELECT 'ISO UTC', CAST('1980-02-16Z' AS datetime)
SELECT 'ISO + Zeit', CAST('1980-02-16T15:30:45' AS datetime)
SELECT 'ISO + Zeit Z', CAST('1980-02-16T15:30:45Z' AS datetime)
SELECT 'ISO + Offset', CAST('1980-02-16T15:30:45+01:00' AS datetimeoffset)	-- nur DATETIME2/DATETIMEOFFSET
GO

 

Mehr dazu siehe Tibor Karaszi's Guide:
http://www.insidesql.org/blogs/frankkalis/2010/08/19/der-ultimative-guide-fuer-die-datetime-datentypen

Gruß Elmar