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

Specify the dates in yyyy-mm-dd format instead, so there is no ambiguity about whether your format is dd-mm-yy or mm-dd-yy or even something else. The computer cannot automatically tell which format you intended. Not every culture uses the same format for date strings, and some of them are hard to tell apart. But yyyy-mm-dd doesn't overlap with any other common format, so it's regarded as a foolproof, non culture-specific string format for dates — ADyson, Apr 25 '20 at 08:18
Does this answer your question? [SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value](https://stackoverflow.com/questions/20838344/sql-the-conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in) — Michael Freidgeim, Aug 25 '23 at 08:39

score 7 · Accepted Answer · answered Apr 25 '20 at 08:30

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');

score 2 · Answer 2 · answered Apr 25 '20 at 08:19

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)

answered Apr 25 '20 at 08:19

zealous

7,336
4
16
36

please edit the entire code and send...Not able to understand how to use this...Should i use this before insert or after insert command etc? – noob Apr 25 '20 at 08:21
@ShailajaGuptaKapoor I have provided you the link. Please check and update according your needs. – zealous Apr 25 '20 at 08:22

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

2 Answers2