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

Question

I am trying to create this table below, but I get the error as shown:

CREATE TABLE Worker 
(
    WORKER_ID INT NOT NULL PRIMARY KEY,
    FIRST_NAME CHAR(25),
    LAST_NAME CHAR(25),
    SALARY INT,
    JOINING_DATE DATETIME,
    DEPARTMENT CHAR(25)
);

INSERT INTO Worker 
    (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES
        (001, 'Monika', 'Arora', 100000, '20-02-2014 09.00.00', 'HR'),
        (002, 'Niharika', 'Verma', 80000, '11-06-2014 09.00.00', 'Admin'),
        (003, 'Vishal', 'Singhal', 300000, '20-02-2014 09.00.00', 'HR'),
        (004, 'Amitabh', 'Singh', 500000, '20-02-2014 09.00.00', 'Admin'),
        (005, 'Vivek', 'Bhati', 500000, '11-06-2014 09.00.00', 'Admin'),
        (006, 'Vipul', 'Diwan', 200000, '11-06-2014 09.00.00', 'Account'),
        (007, 'Satish', 'Kumar', 75000, '20-01-2014 09.00.00', 'Account'),
        (008, 'Geetika', 'Chauhan', 90000, '11-04-2014 09.00.00', 'Admin');

Error:

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

I don't understand why this error. I have clearly specified datetime as the type of joining_date. Still this error

EDIT: After comments below changed date format to dd-mm-yyyy still the same error

Answer 1

There are two issues in you query in the insert part of the statement. Firstly time separator should be (:) not dot (.) secondly the year should be written in in full for example '20-02-14 09:00:00' should change to '2020-02-14 09:00:00'

Your amended query below
CREATE TABLE Worker 
(
    WORKER_ID INT NOT NULL PRIMARY KEY,
    FIRST_NAME CHAR(25),
    LAST_NAME CHAR(25),
    SALARY INT,
    JOINING_DATE DATETIME,
    DEPARTMENT CHAR(25)
);

INSERT INTO Worker 
    (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES
        (001, 'Monika', 'Arora', 100000, '2020-02-14 09:00:00', 'HR'),
        (002, 'Niharika', 'Verma', 80000, '2011-06-14 09:00:00', 'Admin'),
        (003, 'Vishal', 'Singhal', 300000, '2020-02-14 09:00:00', 'HR'),
        (004, 'Amitabh', 'Singh', 500000, '2020-02-14 09:00:00', 'Admin'),
        (005, 'Vivek', 'Bhati', 500000, '2011-06-14 09:00:00', 'Admin'),
        (006, 'Vipul', 'Diwan', 200000, '2011-06-14 09:00:00', 'Account'),
        (007, 'Satish', 'Kumar', 75000, '2020-01-14 09:00:00', 'Account'),
        (008, 'Geetika', 'Chauhan', 90000, '2011-04-14 09:00:00', 'Admin');

Answer 2

Most likely SQL Server is trying to parse your data in another format that you are providing.

check this table and set the format according to it.

for example

convert(datetime, '2020-02-15 21:10:09', 120)

120 = yyyy-mm-dd hh:mi:ss (24h)