Error message when inserting date into table

Question

when I try to insert my date values in all my other tables it works fine, except of one. Whatever format I try I always get the error, that the inserted value couldn't be converted from an input char to the expected date format.

That's how I insert my values

-- ServiceTicket
INSERT INTO ServiceTicket
VALUES ('90000', '01-5-2019', '50000', '10000', '70000', 200.00, 100.00, 5.00, 350.00) --Error converting into DATE type

And this is the table structure:


CREATE TABLE dbo.ServiceTicket (
  ticketIssueNo INT NOT NULL IDENTITY(1,1) PRIMARY KEY, --Identity autoincrements
  serviceDate DATE NOT NULL,
  vehicleId CHAR(8) NOT NULL,
  customerId CHAR(8) NOT NULL,
  inspectionId CHAR(8) NOT NULL,
  serviceCost DECIMAL(10,4) NOT NULL CHECK(serviceCost BETWEEN 0.0 AND 99999.0) DEFAULT 0.0,
  inspectionCost DECIMAL(10,4) NOT NULL CHECK(inspectionCost BETWEEN 0.0 AND 99999.0) DEFAULT 0.0,
  repairCost DECIMAL(2,2) NOT NULL CHECK(repairCost BETWEEN 0.0 AND 99999.0) DEFAULT 0.0,
  GST DECIMAL(10,4) NOT NULL DEFAULT 0.0,
  amountDue DECIMAL(10,4) NOT NULL CHECK(amountDue BETWEEN 0.0 AND 99999.0) DEFAULT 0.0,
  FOREIGN KEY(vehicleId) REFERENCES Vehicle(vehicleId)
  ON UPDATE NO ACTION,
  FOREIGN KEY(inspectionId) REFERENCES VehicleInspection(inspectionId)
  ON UPDATE NO ACTION,
  FOREIGN KEY(customerId) REFERENCES Customer(customerId)
  ON UPDATE NO ACTION
)
GO

I might overlook something.

some day will come (I wish) when I will no longer feel the need to update every SQL question with a DDL statement, but it is this day. +1. — Zohar Peled, Apr 01 '19 at 06:18

Zohar Peled · Answer 1 · 2019-04-01T06:24:06.097

Always list the columns when writing an insert statement.

Here's an exact equivalnet of your insert statement, written properly, based on the DDL you've published:

INSERT INTO ServiceTicket
(serviceDate, vehicleId     , customerId, inspectionId  , serviceCost   , inspectionCost, repairCost, GST   , amountDue) VALUES
('90000'    , '01-5-2019'   , '50000'   , '10000'       , '70000'       , 200.00        , 100.00    , 5.00  , 350.00)

I've used tabs so that each value would be perfectly aligned with the column it goes into, that helps a lot when you have a long list of columns.
As you can clearly see, the serviceDate gets the value '90000' - while it shoud clearly be '01-5-2019'

Always use ISO8601 format for string representation of date / datetime values.
Any other format is culture dependent, and the worst thing about it is that it depends on the default language of the login - so different logins might have different results if you use a culture-dependent format. The ISO8601 standard provides two alternatives for datetime formats: yyyy-mm-ddThh:mm:ss or yyyymmddThhmmss. If you are inserting only a date only string value into a DateTime data type column / variable, be sure to use only the second (yyyymmdd) format - because yyyy-mm-dd is still culture dependent with DateTime (but not with Date or with DateTime2 - that's one more reason why you should never use DateTime again.

So the proper way of writing the insert statement would be this:

INSERT INTO ServiceTicket
(serviceDate, vehicleId     , customerId, inspectionId  , serviceCost   , inspectionCost, repairCost, GST   , amountDue) VALUES
('2019-05-01', '90000'      , '50000'   , '10000'       , '70000'       , 200.00        , 100.00    , 5.00  , 350.00)

(That is, assuming 01-5-2019 stands for May 1st. If it stands for January 5th, it should be 2019-01-05).

thank you very much I tried the different format and I always run into the same error. All the other tables with DATE types seem to be completely fine. — Alica, Apr 01 '19 at 06:59
I tried out DATE with the respected ISO8601 standard and even changed the type to DATETIME as followed: -- ServiceTicket INSERT INTO ServiceTicket VALUES ('90000', '2019-05-01 06:33:59.123456', '50000', '10000', '70000', 200.00, 100.00, 5.00, 350.00) but it does not change anything about the error. Its is constantly the Database error code: 241 @Zohar_Peled — Alica, Apr 01 '19 at 07:25
Because the date format is only a part of the problem. The other part of the problem is, as I've clearly stated in the first part of my query, that you are attempting to insert `'90000'` into the `date` column. Also, you should not use `DateTime`. If you only need a date, use the `Date` data type. if you need date with time, use `DateTime2`. And one last thing, the ISO8601 use `T` as a separator between the date part and the time part, not space. — Zohar Peled, Apr 01 '19 at 07:31
Now you are attempting to insert `'70000'` into `serviceCost`. Use the tab technique I've shown in my answer, it really helps to see what value is going to what column. — Zohar Peled, Apr 01 '19 at 07:49
[Glad to help :-)](http://meta.stackoverflow.com/questions/291325/how-to-show-appreciation-to-a-user-on-stackoverflow/291327#291327) — Zohar Peled, Apr 01 '19 at 08:48

Tim Biegeleisen · Answer 2 · 2019-04-01T06:17:54.573

0

The date format '01-5-2019' which you are using does not appear to be a default accepted date literal by SQL Server. Try using '20190501':

INSERT INTO ServiceTicket (serviceDate, ... <other columns>)
VALUES
    ('20190501', ...);

Note that YYYYMMDD is an unambiguous unseparated ISO format, as the documentation discusses.

edited Apr 01 '19 at 06:17

answered Apr 01 '19 at 05:02

Tim Biegeleisen

502,043
27
286
360

And not even a word about failing to specify the columns list? I mean, I would expect you to at least mention it (I've seen some of your answers before, I know you know better...) – Zohar Peled Apr 01 '19 at 06:09
@ZoharPeled The funny (or maybe sad) thing is that my answer might have actually worked, totally sending the wrong message :P – Tim Biegeleisen Apr 01 '19 at 06:12
No, it wouldn't, because `ticketIssueNo` is an identity column... I'm guessing that's what confused the OP to begin with. – Zohar Peled Apr 01 '19 at 06:13

Amin · Answer 3 · 2019-04-01T11:37:33.480

0

The default format for the year data type in SQL is YYYY-MM-DD(If you've not changed it before) Info. So as described here, you should convert the input string into a valid date type.

edited Apr 01 '19 at 11:37

answered Apr 01 '19 at 05:09

Amin

130
2
13

actually, that's wrong. The correct format is ISO8601. (yyyymmdd or yyyy-mm-dd). – Zohar Peled Apr 01 '19 at 06:36

Mehrnoosh · Answer 4 · 2019-04-01T06:56:17.030

0

The format for inserting date type in SQL is YYYY-dd-MM, You need to change it to '2019-05-01'

edited Apr 01 '19 at 06:56

answered Apr 01 '19 at 05:44

Mehrnoosh

879
2
12
27

actually, that's wrong. The correct format is ISO8601. (yyyymmdd or yyyy-mm-dd) – Zohar Peled Apr 01 '19 at 06:36
I meant the same – Mehrnoosh Apr 01 '19 at 06:56

Santosh Jadi · Answer 5 · 2019-04-01T07:02:45.303

0

Change your date format from '01-5-2019' to '2019-05-01'.

Instead passing date as '01-5-2019' pass CAST('01-5-2019' AS DATE).

edited Apr 01 '19 at 07:02

answered Apr 01 '19 at 06:14

Santosh Jadi

1,479
6
29
55

That would be a great comment but a very low quality answer.... Also, it will not help in this particular case. And have been suggested 4 times already. – Zohar Peled Apr 01 '19 at 06:15

Error message when inserting date into table

5 Answers5