Search code examples
sqlsql-serverssmsdate-formatting

SQL Error When Inserting Date Values into a Table: Conversion Failed from Character String

I'm encountering an error while trying to insert data into a SQL table, specifically into a DATE column. The dates are formatted as YYYY-MM-DD (e.g., 2010-01-12), but I keep receiving a conversion error.

Here's the error message:

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

The structure of the relevant table (BOOK) is as follows:

BookName (PK, VARCHAR(45), not null)
Genre (VARCHAR(25), not null)
DateOfPublication (DATE, not null)
NoOfPages (INT, not null)
WriterID (PK, FK, VARCHAR(11), not null)
EditorID (FK, VARCHAR(11), not null)

Here is an example of one of the insert statements that is causing the error:

INSERT INTO BOOK (BookName, Genre, DateOfPublication, NoOfPages, WriterID, EditorID)
VALUES ('Valley of Heroes', 'Fiction', '2010-01-12',

I have tried different date formats but still encounter the same error.

How should I correctly format the DateOfPublication field in my insert queries to comply with the SQL DATE type?

Solution

  • It seems that you've an error in your values list. The value '10' should be at the end..

    Your statement 

    insert into BOOK (BookName, Genre, DateOfPublication, NoOfPages, WriterID, EditorID)
values ('Valley of Heroes','10','Fiction','2010-01-12',874,'20');

    would try to add the value 'Fiction' into the DateOfPublication column.

    To correct it use 

    insert into BOOK (BookName, Genre, DateOfPublication, NoOfPages, WriterID, EditorID)
values ('Valley of Heroes','Fiction','2010-01-12',874,'10','20');