Can anyone help correct me for below conversions from SQL server to Oracle?

1. I only need month value 'MM' from below substraction. datediff(month, date1, date2) --> SUBTR(TO_NUMBER(date2- date1),6,7);
2. I need 'YYYYMM' value from below result, but the system shows 'ORA-00904: "MM": invalid identifier' . CONVERT(varchar, YEAR(date1)) format(date1, 'MM') --> TO_CHAR(i.ordr_clsd_dt,YYYY) TO_CHAR(i.ordr_clsd_dt,MM)

date1 and date2 are all date type data.

CodePudding user response：

In Oracle, you can simply subtract two dates and get the difference in days.

SELECT TO_DATE('2021-11-02', 'YYYY-MM-DD') -  
       TO_DATE('2021-10-31', 'YYYY-MM-DD') AS DateDiff
FROM   dual

DATEDIFF
2

CodePudding user response：

Check function MONTHS_BETWEEN

When you have the number of months between two dates, then you just need some formatting. You can use numeric value of months, or convert to an INTERVAL YEAR TO MONTH value

WITH t AS
    (SELECT DATE '2020-08-01' date1, DATE '2021-11-01' date2 FROM dual),
mb AS
    (SELECT date1, date2, MONTHS_BETWEEN(date2, date1) AS MONTHSBETWEEN FROM t)
SELECT date1, date2, MONTHSBETWEEN,
    LPAD(TRUNC(MONTHSBETWEEN/12), 4, '0') || LPAD(MOD(MONTHSBETWEEN, 12), 2, '0'),
    NUMTOYMINTERVAL(MONTHSBETWEEN, 'MONTH'),
    LPAD(EXTRACT(YEAR FROM NUMTOYMINTERVAL(MONTHSBETWEEN, 'MONTH')) , 4, '0') || LPAD(EXTRACT(MONTH FROM NUMTOYMINTERVAL(MONTHSBETWEEN, 'MONTH')), 2, '0')
FROM mb;