varchar数据类型到datetime数据类型的转换导致值超出范围。SQL服务器

noob 发表于 Dev

152

菜鸟

我正在尝试在下面创建此表，但出现如下错误：

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

错误：

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

我不明白为什么会出现此错误。我已经明确指定datetime作为join_date的类型。还是这个错误

编辑：下面的注释将日期格式更改为dd-mm-yyyy之后仍然是相同的错误

强威

在语句的插入部分中查询有两个问题。首先，时间分隔符应为（:)而不是点（。）。第二年应完整写出年份，例如'20-02-14 09:00:00'应更改为'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');

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。