0

I'm trying to insert values into a table. Currently there are 80 insert statements.
74 of them work perfectly fine. In 6 of them i get the following error :

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I don't have a clue why, since they are not different then the others.
Here is some example data:

CREATE TABLE Chair
(
  ChairID INT,
  RoomID INT,
  xyz INT,
  zyx INT,
  time DATETIME,
  CONSTRAINT pkChar PRIMARY KEY(ChairID, RoomID,time)
);

These insert statements work:

INSERT INTO Chair
  VALUES (1,1,1,32,CONVERT(DATETIME,'12.01.2017 08:32:12'));
INSERT INTO Chair
  VALUES (1,1,0,50,CONVERT(DATETIME,'12.01.2017 08:33:12'));

And these don't seem to work:

INSERT INTO Chair
  VALUES (2,1,1,63,CONVERT(DATETIME,'16.02.2017 08:00:34'));  
INSERT INTO Chair
  VALUES (2,1,0,91,CONVERT(DATETIME,'16.02.2017 08:54:12'));

I'm thankful for any approach to solve this issue!

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
Simon SCHÖNEGGER
  • 107
  • 1
  • 6
  • What is your language set to on your login? My guess is you're using a language where 16.02.2017 means the 2nd day of the 16th month (which doesn't exist). This would also mean that the inserts you have done so far may have the incorrect data stored. – Thom A Mar 04 '18 at 12:14

1 Answers1

3

The error is because your session DATEFORMAT setting is not DMY. Execute DBCC USEROPTIONS to verify.

I suggest you use an ISO 8601 format like '2017-02-16T08:54:12' to avoid ambiguity and dependence on the session settings.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71