I am trying to fill tables with values but I got conversion failed error

I am creating a database and I am trying to fill table with values but I got error

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

Code of the query:

INSERT INTO Register_of_crimes (id_crime , Date, List_of_crime , Fine)
VALUES ('1', '1989-3-6', 'Rušenie nočného kľudu', '30'),
       ('2', '1991-3-3', 'Poškodzovanie cudzej veci', '50'),
       ('3', '2011-3-3', 'Pitie alkoholu na verejnosti', '20'),
       ('4', '1999-6-5', 'Výtržnosti na verejnosti', '40')

Table I created :

CREATE TABLE Register_of_crimes
(
    id_crime int,
    Date date,
    List_of_crime varchar (150),
    Fine money,
    PRIMARY KEY (id_crime)
)

Thank you for all your answers and advice

Solution 1:

You should actually use 19910303, no dashes.

SQL Server interprets 1991-03-03 as YYYY-DD-MM in most languages, as this demonstrates:

SET LANGUAGE Deutsch;    
SELECT CONVERT(datetime, '1991-03-13');

Result:

Msg 242, Level 16, State 3
Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.

(It actually works fine if you convert to date, but that is an exception, and it is much better practice to just always the same, unambiguous format.)

• Example db<>fiddle
• Plenty more about dates in Dating Responsibly

You also should make sure that strings that may contain Unicode characters are declared with the right data type (and potentially the right collation), and all string literals are prefixed with N. And also integers to not be surrounded by string delimiters. So I would expect:

List_of_crime nvarchar(150)
--------------^

...and...

VALUES (1, '19890306', N'Rušenie nočného kľudu', 30)

Solution 2:

Use a unambiguous date and time format. In SQL Server those are yyyyMMdd and yyyy-MM-ddThh:mm:ss.nnnnnnn. Also when your data types are numerical don't wrap them in single quotes ('), that is for literal strings. I assume your values were in the format yyyy-M-d:

INSERT INTO Register_of_crimes (id_crime , Date, List_of_crime , Fine)
VALUES (1,'19890306','Rušenie nočného kľudu', 30),
       (2,'19910303','Poškodzovanie cudzej veci',50),
       (3,'20110303','Pitie alkoholu na verejnosti',20),
       (4,'19990605','Výtržnosti na verejnosti',40);

On a separate note, you may find that the values you insert for List_of_crime suffer data loss, due to the characters being outside of the code page (for example ľ may be implicitly converted to l). If this is the case you'll need to ALTER your table so that List_of_crime is an nvarchar and then use nvarchar literal strings, such as N'Rušenie nočného kľudu'.