oracle - 在Oracle/PLSQL中增加Datetime时出错

在这里，我使用从StartDateTime到EndDatetime的循环，并在每次迭代中增加1小时。一切都在循环中工作，但问题出在插入查询中，请检查插入查询。

declare
StartDateTime   TIMESTAMP :=to_date( '2017-01-01 00:00:00','yyyy-mm-dd
hh24:mi:ss');
EndDateTime   TIMESTAMP :=to_date( '2017-12-31 00:00:00','yyyy-mm-dd
hh24:mi:ss');
dateti TIMESTAMP;
dateti2 TIMESTAMP;
StartDateTime1 TIMESTAMP;
sub INTEGER;
semester Number;
begin

sub:=( CAST( EndDateTime AS DATE ) - CAST( StartDateTime AS DATE ) )  ;

FOR i IN  0 .. 1
LOOP
StartDateTime1:=StartDateTime+i;
for idx in 0..2 loop
dateti:=to_date(StartDateTime1+(idx/24.0),'yyyy-mm-dd hh24:mi:ss');
dateti2:=to_date(StartDateTime1+((idx+1)/24.0)+ interval '-1' second,'yyyy-
mm-dd hh24:mi:ss');

      case
        when to_number(to_char(dateti ,'Q'))>6 then semester:=to_number(2);
       else semester:=to_number(1);
        end case;

问题从这里开始插入查询。它说找到非数字字符.DateSlotStart和DateSlotEnd数据类型是Timestramp。请查看图片并告知我应该更改什么？

  insert into DimDate1(DateSlotStart,DateSlotEnd,
  "Date",SlotName,MonthName,MonthNumberOfYear,Quarter,Year,Semester) values
   (to_date(dateti ,'yyyy-mm-dd hh24:mi:ss') ,to_date(dateti2 ,'yyyy-mm-dd
    hh24:mi:ss') ,
   to_date(dateti ,'yyyy-mm-dd'),to_char(dateti ,'hh24:mi' )||' To
   '||to_char(dateti2 ,'hh24:mi' ),to_char(dateti
    ,'Month'),to_number(to_char(dateti ,'mm')),to_number(to_char(dateti ,'Q')),
  to_number(to_char(dateti ,'YYYY')) , semester);

end loop;


   END LOOP;
  end;
/

请同时检查这个

最佳答案

问题是您正在尝试将TIMESTAMP转换为不需要日期的日期。当您需要将CAST转换为DATE时，总是更好。以此替换您的插入内容。它应该工作。

   INSERT INTO dimdate1 (
        dateslotstart,
        dateslotend,
        "Date",
        slotname,
        monthname,
        monthnumberofyear,
        quarter,
        year,
        semester
    ) VALUES (
        dateti,
        dateti2,
        CAST (dateti AS DATE),
        TO_CHAR(dateti,'hh24:mi')
        || ' To '
        || TO_CHAR(dateti2,'hh24:mi'),
        TO_CHAR(dateti,'Month'),
        to_number(TO_CHAR(dateti,'mm') ),
        to_number(TO_CHAR(dateti,'Q') ),
        to_number(TO_CHAR(dateti,'YYYY') ),
        semester
    );

关于oracle - 在Oracle/PLSQL中增加Datetime时出错，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47123149/