如何将日期格式为MM/DD/YYYY的insert语句插入到下面这样的oracle create table中？

Question

下面是有问题的表和语句，当我尝试输入日期时，它会导致错误

CREATE TABLE cteam_ExpenseReport ( 
    ERNo NUMBER NOT NULL, 
    ERDesc VARCHAR2(50) NOT NULL,     
    ERSubmitDate DATE,
    ERStatusDate DATE ,
    ERStatus CHAR(8)NOT NULL, 
    SubmitUserNo NUMBER NOT NULL, 
    ApprUserNo NUMBER NOT NULL

);


INSERT INTO cteam_ExpenseReport 
(ERNo, ERDesc, ERSubmitDate, ERStatusDate, ERStatus, SubmitUserNo, ApprUserNo)
VALUES (1,'Sales Presentation','8/10/2002','8/26/2002','APPROVED',3,4);

Answer 1

字符串不能直接转换为日期。您可以使用to_date或date函数，如下所示：

INSERT INTO cteam_ExpenseReport 
(ERNo, ERDesc, ERSubmitDate, ERStatusDate, ERStatus, SubmitUserNo, ApprUserNo)
VALUES (1, 'Sales Presentation',
    TO_DATE('08/10/2002', 'MM/DD/YYYY'), 
    TO_DATE('08/26/2002', 'MM/DD/YYYY'),    
    'APPROVED', 3, 4 );

或

INSERT INTO cteam_ExpenseReport 
    (ERNo, ERDesc, ERSubmitDate, ERStatusDate, ERStatus, SubmitUserNo, ApprUserNo)
    VALUES (1, 'Sales Presentation',
        DATE '2002-08-10',
        DATE '2002-08-26',   
        'APPROVED', 3, 4 );

DATE函数接受YYYY-MM-DD格式。

干杯！！

Answer 2

使用TO_DATE()将字符串转换为DATE

INSERT INTO cteam_ExpenseReport 
(ERNo, ERDesc, ERSubmitDate, ERStatusDate, ERStatus, SubmitUserNo, ApprUserNo)
VALUES (
    1,
    'Sales Presentation',
    TO_DATE('8/10/2002', 'MM/DD/YYYY'),
    TO_DATE('8/26/2002', 'MM/DD/YYYY'),
    'APPROVED',
    3,
    4
);