Oracle和JDE日曆轉換

普通日期->儒日曆：

方法1:

select substr(TO_date('2019-06-10','yyyy-mm-dd'),2,1)+1||substr(TO_date('2019-06-10','yyyy-mm-dd'),3,2)||to_char(to_date('2019-06-10','yyyy-mm-dd'),'DDD') from dual;

方法2:

SELECT TO_CHAR(TO_DATE('1929-05-29','YYYY-MM-DD'),'YYYYDDD')-1900000 FROM DUAL;

--開始和結束日期換算：

SELECT TO_CHAR(TO_DATE('2023-05-01','YYYY-MM-DD'),'YYYYDDD')-1900000 start_date,TO_CHAR(TO_DATE('2023-05-31','YYYY-MM-DD'),'YYYYDDD')-1900000 end_date FROM DUAL;

--sysdate->儒日曆：

SELECT TO_CHAR(sysdate,'YYYYDDD')-1900000 FROM DUAL;

儒日曆->普通日期

select to_date(substr(119162,4,3),'ddd') from dual;

###日期轉時間戳

select cast(to_date('2016/8/19','yyyy-mm-dd,hh24:mi:ss') as timestamp) date_to_timestamp  from dual;

###時間戳-》date

select TO_CHAR(to_timestamp('2016/8/19','yyyy-mm-dd hh24:mi:ss') ,'YYYY-MM-DD HH24:MI:SS')  

  from dual