Блог

Кто-нибудь может помочь мне исправить приведенные ниже преобразования из SQL server в Oracle ?

1. Мне нужно только значение месяца «ММ» из вычитания ниже. datediff(month, date1, date2) —> SUBTR(TO_NUMBER(date2- date1),6,7);
2. Мне нужно значение ‘YYYYMM’ из приведенного ниже результата, но система показывает ‘ORA-00904: «MM»: недопустимый идентификатор’. ПРЕОБРАЗОВАТЬ(varchar, ГОД (date1)) формат(date1, ‘MM’) —> TO_CHAR(i.ordr_clsd_dt,ГГГГ) TO_CHAR(i.ordr_clsd_dt,ММ)

date1 и date2 все date данные типа.

Проверка функции MONTHS_BETWEEN

Когда у вас есть количество месяцев между двумя датами, вам просто нужно некоторое форматирование. Вы можете использовать числовое значение месяцев или преобразовать в INTERVAL YEAR TO MONTH значение

 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;
 

Хотя решение Вернфрида намного более полное и отображает дополнительную информацию, вы можете получить упрощенный вывод с помощью следующего запроса, который я показываю ниже.

Примечание: Months_between может возвращать дробь. Чтобы удалить дробь, я использовал команду trunc .

 SELECT trunc (Months_between(To_date('2021103', 'YYYYMMDD'),
       To_date('20210401', 'YYYYMMDD')))
       num_months
FROM   dual;

NUM_MONTHS
6

 

Вычисление с использованием месяцев всегда является чем-то особенным, поскольку месяцы имеют разную длину. Давайте посмотрим, что здесь делает SQL Server:

 select datediff(month, '20210131', '20210201');
 

=> 1

SQL Server игнорирует, что это всего лишь один день. Дни относятся к двум разным смежным месяцам, поэтому ответ — разница в 1 месяц.

Oracle MONTHS_BETWEEN , с другой стороны, пытается вычислить точную разницу в долях месяцев:

 select months_between(date '2021-02-01', date '2021-01-31') from dual;
 

=> 0.032258

Если бы я хотел посмотреть месяцы только в Oracle, я бы использовал некоторые простые вычисления, например:

 select
  (extract(year from date2) * 12   extract(month from date2)) -
  (extract(year from date1) * 12   extract(month from date1))
from dates;
 

Что касается вашего второго вопроса: вам нужен YYYYMM, поэтому используйте формат ‘YYYYMM’. Формат представляет собой строковый литерал, поэтому нам нужны одинарные кавычки. Без них Oracle подумала бы, что это должно быть имя столбца. Вот почему Oracle сообщает вам, что вы используете недопустимый идентификатор в своем запросе, поскольку столбец mm не существует.

 TO_CHAR(i.ordr_clsd_dt, 'yyyymm')
 

Или в виде числового значения:

 TO_NUMBER(TO_CHAR(i.ordr_clsd_dt, 'yyyymm'))
 

В Oracle вы можете просто вычесть две даты и получить разницу в днях.

 
select 
  dt1, dt2,
  trunc( months_between(dt2,dt1) ) mths, 
  dt2 - add_months( dt1, trunc(months_between(dt2,dt1)) ) days
from
(
    select date '2021-01-01' dt1, date '2021-03-25' dt2 from dual union all
    select date '2017-01-01' dt1, date '2021-01-01' dt2 from dual union all
    select date '2021-01-01' dt1, date '2021-01-01' dt2 from dual union all
    select date '2012-02-28' dt1, date '2021-03-01' dt2 from dual union all
    select date '2021-02-28' dt1, date '2021-03-01' dt2 from dual union all
    select date '2021-02-28' dt1, date '2021-04-01' dt2 from dual union all
    select trunc(sysdate-1)  dt1, sysdate               from dual
) sample_data

Results

DT1    DT2    MTHS    DAYS
01-JAN-21    25-MAR-21    2    24
01-JAN-17    01-JAN-21    48    0
01-JAN-21    01-JAN-21    0    0
28-FEB-12    01-MAR-21    108    1
28-FEB-21    01-MAR-21    0    1
28-FEB-21    01-APR-21    1    1
01-DEC-21    02-DEC-21    0    1.98178240740740740740740740740740740741