仕事で
SQL Server 2005から、Oracle11gR2への移植をしてるんだけど、
SQL Developerで移行したDATEDIFF関数がおかしい。
SQL Server 2005
DATEDIFF(day, '2015-08-02 00:00:00', '2015-08-02 00:00:00') ⇒ 0
DATEDIFF(day, '2015-08-02 00:00:00', '2015-08-02 12:00:00') ⇒ 0
DATEDIFF(day, '2015-08-02 00:00:00', '2015-08-03 00:00:00') ⇒ 1
DATEDIFF(day, '2015-08-02 00:00:00', '2015-08-03 08:59:00') ⇒ 1
DATEDIFF(day, '2015-08-02 00:00:00', '2015-08-03 09:00:00') ⇒ 1
DATEDIFF(day, '2015-08-02 00:00:00', '2015-08-03 23:59:00') ⇒ 1
DATEDIFF(day, '2015-08-02 00:00:00', '2015-08-04 00:00:00') ⇒ 2
Oracle 11gR2
utils.DATEDIFF(day, '2015-08-02 00:00:00', '2015-08-02 00:00:00') ⇒ 0
utils.DATEDIFF(day, '2015-08-02 00:00:00', '2015-08-02 12:00:00') ⇒ 1 <えっ
utils.DATEDIFF(day, '2015-08-02 00:00:00', '2015-08-03 00:00:00') ⇒ 1
utils.DATEDIFF(day, '2015-08-02 00:00:00', '2015-08-03 08:59:00') ⇒ 1
utils.DATEDIFF(day, '2015-08-02 00:00:00', '2015-08-03 09:00:00') ⇒ 2 <えっ
utils.DATEDIFF(day, '2015-08-02 00:00:00', '2015-08-03 23:59:00') ⇒ 2 <えっ
utils.DATEDIFF(day, '2015-08-02 00:00:00', '2015-08-04 00:00:00') ⇒ 2
oracle側の日時リテラルについては、to_date('2015/08/02 00:00:00','yyyy/mm/dd hh24:mi:ss')のように脳内補完してください・・・
ちなutilsパッケージは、Oracleが移行時に勝手に作るEMULATIONスキーマが持っている模様。
たのむぜマジで。
対応は未着手ですが、
こんな感じで置き換えようかと画策中。
select trunc(おおきいdate - ちいさいdate) from dual;
対応したら追記します。。。
追記
trunc(おおきいdate - ちいさいdate)でいけました。
oracleのutils.DATEDIFFの各オーバーロードは、
utils.datediff_というprivate関数に集約されていました。
EMULATION.utilsパッケージ内
FUNCTION DATEDIFF(P_DATEPART IN VARCHAR2, P_START_DATE_STR IN VARCHAR2, P_END_DATE_EXPR IN TIMESTAMP) RETURN NUMBER
IS
BEGIN
RETURN datediff_(p_datepart, CONVERT_STRING_TO_TIMESTAMPTZ(TRIM(p_start_date_str)), p_end_date_expr);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END DATEDIFF;
FUNCTION DATEDIFF(P_DATEPART IN VARCHAR2, P_START_DATE_STR IN VARCHAR2, P_END_DATE_EXPR IN TIMESTAMP WITH TIME ZONE) RETURN NUMBER
IS
BEGIN
RETURN datediff_(p_datepart, CONVERT_STRING_TO_TIMESTAMPTZ(TRIM(p_start_date_str)), p_end_date_expr);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END DATEDIFF;
・・・つづく
んで、ちょっと上のほうにdatediff_があったので、ここを書き換えました。
EMULATION.utilsパッケージ内書き換え後
FUNCTION datediff_(p_datepart IN VARCHAR2, p_start_date_expr IN TIMESTAMP WITH TIME ZONE, p_end_date_expr IN TIMESTAMP WITH TIME ZONE)
RETURN NUMBER
IS
v_ret_value NUMBER := NULL;
v_part VARCHAR2(15);
v_start_ts_tz TIMESTAMP WITH TIME ZONE;
v_end_ts_tz TIMESTAMP WITH TIME ZONE;
v_start_date DATE;
v_end_date DATE;
temp_date_1 DATE;
temp_date_2 DATE;
temp_char_1 VARCHAR2(10);
temp_char_2 VARCHAR2(10);
BEGIN
・・・
ElSIF v_part IN ('DAYOFYEAR', 'DY', 'Y') THEN
v_ret_value := ROUND(CAST(v_end_ts_tz AS DATE) - CAST(v_start_ts_tz AS DATE));
ElSIF v_part IN ('DAY', 'DD', 'D') THEN
-- 変更箇所こっから!!!!!!!!!!!!!!!!!!!
--v_ret_value := ROUND(v_end_date - v_start_date);
temp_char_1 := TO_CHAR(v_end_ts_tz, 'YYYY/MM/DD');
temp_char_2 := TO_CHAR(v_start_ts_tz, 'YYYY/MM/DD');
temp_date_1 := TO_DATE(temp_char_1);
temp_date_2 := TO_DATE(temp_char_2);
v_ret_value := TRUNC(temp_date_1 - temp_date_2);
-- 変更箇所ここまで!!!!!!!!!!!!!!!!!!!
ElSIF v_part IN ('WEEK', 'WK', 'WW') THEN
v_ret_value := ROUND((CAST(v_end_ts_tz AS DATE) - CAST(v_start_ts_tz AS DATE)) / 7);
・・・
なぜか文字列を介さないとうまくいかない・・・(と開発メンバーからの声)
たのむぜoracle(迫真