CODEWITHSUNDEEP
sqlerror-handlingoracle11gtruncation
Starbucks
Starbucks

Reputation: 1568

SQL Data truncation: Incorrect date value

I am getting this error.

Data truncation: Incorrect date value: '18-JUN-13' for column 'dateFrom' at row 1

What could be the reason of this error?

INSERT INTO Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) VALUES ('01', '01', '18-JUN-13', '26-NOV-13', '01'); 
INSERT INTO Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) VALUES ('02', '01', '18-JUN-85', '26-JUN-85', '02'); 
INSERT INTO Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) VALUES ('02', '03', '18-JUN-66', '26-JUN-66', '01'); 
INSERT INTO Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) VALUES ('04', '02', '18-OCT-13', '23-OCT-13', '01'); 
INSERT INTO Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) VALUES ('02', '03', '18-JUL-87', '26-AUG-87', '01'); 
INSERT INTO Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) VALUES ('02', '03', '18-AUG-87', '26-AUG-87', '01'); 
INSERT INTO Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) VALUES ('02', '01', '18-AUG-87', '26-SEP-87', '01');

Upvotes: 3

Views: 14506

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

If you don't absolutely need to rely on formatted dates, use the SQL standard DATE literal instead, as I've described in this blog post, always. So, instead of:

INSERT INTO Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) 
VALUES ('01', '01', '18-JUN-13', '26-NOV-13', '01');

... write:

INSERT INTO Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) 
VALUES ('01', '01', DATE '2013-06-18', DATE '2013-11-26', '01');

If that format is a requirement (e.g. because you're loading data from some external source), use TO_DATE() to parse it:

INSERT INTO Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) 
VALUES (
  '01', 
  '01', 
  TO_DATE('18-JUN-13', 'DD-MON-YY'), 
  TO_DATE('26-NOV-13', 'DD-MON-YY'), 
  '01'
);

Upvotes: 5

Related Questions