Meldung 242, Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.

[WernerK]

Hallo,

In einer SQL Server DB bzw. Tabelle gibt es in einer Spalte DATUM_BEGINN schon Einträge wie: 2017-09-18 14:30:38.000 Wenn ich nun aber mit SSMS einen Insert versuche, kommt immer diese Meldung:

Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.

INSERT INTO [TESTDB] ([DATUM_BEGINN]) 
            VALUES 
            ('2017-11-16 08:39:05')

Was ist daran falsch bzw. warum lässt der SQL Server via SSMS dies so nicht zu? Eine Webanwendung mit einem PHP Script macht auch diese INSERTS und hier klappt es.

Gruss

Werner

[WernerK]

Hallo

keine eine Idee?

viele Grüße

Werner

[Rolf B]

Hallo WernerK,

SSMS meint: Insert-Statement in einem Query-Fenster?

Probelauf mit SQL Server Management Studio 2012:

CREATE TABLE [dbo].[honk](
	[DATUM_BEGINN] [datetime] NOT NULL
) ON [PRIMARY]

INSERT INTO [honk] ([DATUM_BEGINN]) 
            VALUES 
            ('2017-11-16 08:39:05')

(1 row(s) affected)

Null Problemo!

Wenn ich allerdings vorsätzlich Schrott einsetze (z.B. ein Tab statt eines Space zwischen Datum und Zeit) bekomme ich diese Meldung:

Conversion failed when converting date and/or time from character string.

Rolf

[MudGuard]

Hi,

In einer SQL Server DB bzw. Tabelle gibt es in einer Spalte DATUM_BEGINN schon Einträge wie: 2017-09-18 14:30:38.000 Wenn ich nun aber mit SSMS einen Insert versuche, kommt immer diese Meldung:

Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.

INSERT INTO [TESTDB] ([DATUM_BEGINN]) 
            VALUES 
            ('2017-11-16 08:39:05')

Was ist daran falsch bzw. warum lässt der SQL Server via SSMS dies so nicht zu?

Du verläßt Dich darauf, daß das default-Format für den datetime-Datentyp zu Deinem Datums-String paßt. Üblicherweise haben DBMS Funktionen zur Konvertierung von String zu DateTime, z.B. to_date(datumsstring, formatstring) in Oracle …

Nutze die entsprechende Funktion.

Evtl. wird das 2017-11-16 als year-day-month interpretiert, da paßt die 16 nicht so ganz.

Eine Webanwendung mit einem PHP Script macht auch diese INSERTS und hier klappt es.

Evtl. weil dort das default-Format für date-strings anders gesetzt ist/wird.

cu,
Andreas a/k/a MudGuard

[Rolf B]

Hallo Andreas,

Evtl. wird das 2017-11-16 als year-day-month interpretiert, da paßt die 16 nicht so ganz.

Habe ich auch überlegt, dann aber ausgeschlossen. Datumsformate dieser Welt sind dmy und mdy, dazu ymd für sortierbare Datumsangaben. YDM habe ich noch nie gesehen, und SSMS arbeitet normalerweise auch mit den Date-Einstellungen der Windows-Lokalisierung; in D also DMY. YYYY-MM-DD wird als ISO-Timestamp ebenfalls verstanden.

Kann aber man schnell testen - was passiert bei '2017-11-11 12:34:56'

Warten wir mal den Montag ab, MS SQL Server ist eher kein Hobbyeinsatz und ich denke, vor Montag wird Werner nicht nach einer Antwort schauen. Meine Hypothese ist ja das falsche Whitespace.

Rolf

[MudGuard]

Hi,

Habe ich auch überlegt, dann aber ausgeschlossen. Datumsformate dieser Welt sind dmy und mdy, dazu ymd für sortierbare Datumsangaben. YDM habe ich noch nie gesehen, und SSMS arbeitet normalerweise auch mit den Date-Einstellungen der Windows-Lokalisierung; in D also DMY. YYYY-MM-DD wird als ISO-Timestamp ebenfalls verstanden.

Trump-ISO? 😉

Die Amerikaner stellen ja normalerweise den Tag in die Mitte, wenn die das dann iso-fizieren, steht das Jahr am Anfang, der Tag in der Mitte und der Monat am Ende … 😉

Unabhängig davon, was der konkrete Auslöser ist, sollte m.E. die entsprechende Konvertiermethode mit passendem Formatstring explizit aufgerufen werden, statt sich auf den Zufall, daß der default-Formatstring passen könnte, zu verlassen.

cu,
Andreas a/k/a MudGuard

[Rolf B]

Hallo Andreas,

Die Amerikaner stellen ja normalerweise den Tag in die Mitte, wenn die das dann iso-fizieren, steht das Jahr am Anfang, der Tag in der Mitte und der Monat am Ende … 😉

Nein, sie stellen nicht den Tag in die Mitte, sondern den Monat vor's Datum. Was bei hinten stehendem Jahr natürlich zum gleichen Ergebnis führt... Und „isofizieren“ bedeutet nicht, blindlings das Jahr nach vorn zu holen (und eine angenommene Regel „der Tag kommt in die Mitte“ zu übertragen), sondern ist in ISO 8601 bzw EN 28601 ganz klar als Jahr-Monat-Tag definiert. Entweder YYYY-MM-DD, YYYYMMDD oder zur Not auch YYMMDD.

Unabhängig davon, was der konkrete Auslöser ist, sollte m.E. die entsprechende Konvertiermethode mit passendem Formatstring explizit aufgerufen werden, statt sich auf den Zufall, daß der default-Formatstring passen könnte, zu verlassen.

Wie gesagt, ISO ist ISO und wird vom SQL Server als solches erkannt. Auf Grund der 2017 vorne ist eine andere Interpretation ausgeschlossen. Wobei - es gibt eine kleine Variante: ISO vs ODBC. ISO verlangt eigentlich ein T zwischen Datum und Zeit, ODBC nicht. Ich hatte aber noch nie ein Problem damit, dass SQL Server das nicht korrekt erkannt hätte.

Trotzdem: ein expliter Konvertierungsversuch könnte Klarheit schaffen. Hier die Details.

Rolf

[WernerK]

Hallo an alle,

entschuldigt bitte die verspätete Antwort. Ich kam erst jetzt zum testen.

Tatsächlich liegt es wohl daran wie es Andreas vermutet hat. Die 16 wird als Monat interpretiert. Mache ich ein Datum wie unten, klappt es

INSERT INTO [TESTDB] ([DATUM_BEGINN]) 
            VALUES 
            ('2017-11-12 08:39:05')

Es steht dann so in der Tabelle: 2017-12-11 08:39:05.000

Dann muss wohl das PHP Script schon eine andere Formatierung machen.

vielen Dank

Gruss Werner

[Auge]

Hallo

Tatsächlich liegt es wohl daran wie es Andreas vermutet hat. Die 16 wird als Monat interpretiert. Mache ich ein Datum wie unten, klappt es

INSERT INTO [TESTDB] ([DATUM_BEGINN]) 
            VALUES 
            ('2017-11-12 08:39:05')

Es steht dann so in der Tabelle: 2017-12-11 08:39:05.000

Dann muss wohl das PHP Script schon eine andere Formatierung machen.

Es dürfte sich dabei um Unterschiede der von PHP bzw. vom Management Studio verwendeten Zugriffswege handeln. Es gibt ja mehrere Treiber (ODBC, Native Client, diverse andere OLE-Treiber), die sich unterschiedlich verhalten. Hier mal ein Beispiel des Angebots, um einen MS-SQL-2008-Server zu kontaktieren.

Schlussendlich geht es aber darum, die Regeln, die durch den MS SQL Server vorgeschrieben werden, zu erfüllen. Wenn du im INSERT-Statement das Datum explizit einem CONVERT unterziehst (CONVERT(datetime, '2017-11-16 08:39:05', 121)). Damit passt du die Eingabe garantiert in eines der unterstützten Formate an.

Tschö, Auge