database - 出现 SQL 错误：ORA-01847：运行我的语句时，月中的某天必须介于 1 和月的最后一天之间

Question

伙计们不知道为什么我在标题中收到错误。请参见下文，非常感谢下面插入语句的任何帮助：

提前致谢

INSERT INTO RUN_DATA VALUES ('15-JUL-18','5.00K','64:00','233','6','6','17.13','0')
Error report -
SQL Error: ORA-01847: day of month must be between 1 and last day of month
01847. 00000 -  "day of month must be between 1 and last day of month"
*Cause:    
*Action:


Name             Null Type         
---------------- ---- ------------ 
RUN_DATE              DATE         
RUN                   VARCHAR2(8)  
RUN_TIME              TIMESTAMP(6) 
CALORIES              NUMBER(4)    
SITUPS                NUMBER(3)    
PRESSUPS              NUMBER(3)    
WEIGHT                NUMBER(4,2)  
WEIGHT_LOSS_GAIN      NUMBER(2) 

PARAMETER                      VALUE                                                          
------------------------------ ----------------------------------------------------------------
NLS_DATE_FORMAT                DD-MON-RR                          

SYSDATE 
---------
21-JUL-18

标签： databaseoracle

Answer 1

我建议你使用INTERVAL DAY TO SECONDforRUN_TIME而不是TIMESTAMP

此外，在插入中指定所有列名。

CREATE TABLE RUN_DATA (
RUN_DATE              DATE    ,     
RUN                   VARCHAR2(8) , 
RUN_TIME              INTERVAL DAY TO SECOND ,
CALORIES              NUMBER(4)    ,
SITUPS                NUMBER(3)    ,
PRESSUPS              NUMBER(3)    ,
WEIGHT                NUMBER(4,2)  ,
WEIGHT_LOSS_GAIN      NUMBER(2) 
  );

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR'; --alternatively use DATE literal or TO_DATE


INSERT INTO run_data 
            (run_date, 
             run, 
             run_time, 
             calories, 
             situps, 
             pressups, 
             weight, 
             weight_loss_gain) 
VALUES      ('15-JUL-18', --or DATE '2018-07-15'
             '5.00K', 
             interval '64' minute,  --specifying proper interval
             233, 
             6, 
             6,      --quotes not needed for number types
             17.13, 
             0); 

演示