背景
在对项目进行信创迁移，从 ORALCE 迁移到达梦数据库，为了更好的平滑过渡，对达梦实例 dm.ini 文件中修改 COMPATIBLE_MODE = 2开启兼容 ORACLE 语法的模式。然后使用 DTS 进行迁移时，会将 ORACLE 中的 DATE DATETIME 数据类型自动转换为 TIMESTAMP 类型。且经过验证，后续的建表，如果是包含了 DATE DATETIME 类型，创建后也会被转换为 TIMESTAMP 类型。
这个时候，如果在存储过程中使用 DATEDIFF 函数，会出现数据类型不对的报错，所以这里需要进行相应的改造。
有两个办法：
1）对相应的处理逻辑进行手动修改（解决方法一）
2）在 DTS 迁移时，对 ORACLE - DM 进行数据类型映射（解决方法二，该方法经咨询达梦技术方获取）
解决方法一
原先对于 DATE数据类型使用 DATEDIFF 进行日期相减
现在使用 TRUNC 函数将时间戳转换为日期，再进行日期相减
TRUNC是截断数字，也有截断日期的用法
测试用例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
DROP TABLE IF EXISTS TEST.T_WEATHER;
CREATE TABLE TEST.T_WEATHER(
ID INT,
RECORDDATE DATE,
TEMPERATURE INT
);
INSERT INTO TEST.T_WEATHER VALUES(1, '2024-08-10', 37);
INSERT INTO TEST.T_WEATHER VALUES(2, '2024-08-11', 36);
INSERT INTO TEST.T_WEATHER VALUES(3, '2024-08-12', 38);
INSERT INTO TEST.T_WEATHER VALUES(4, '2024-08-13', 39);
INSERT INTO TEST.T_WEATHER VALUES(5, '2024-08-14', 37);
INSERT INTO TEST.T_WEATHER VALUES(6, '2024-08-15', 37);
COMMIT;
|
原先的处理逻辑
1
2
3
4
5
6
7
8
9
10
11
12
|
SELECT T.*
FROM (
SELECT A.ID,
A.RECORDDATE,
A.TEMPERATURE,
LEAD(A.RECORDDATE, 1, NULL) OVER(ORDER BY A.RECORDDATE) AS DD,
LEAD(A.TEMPERATURE, 1, NULL) OVER(ORDER BY A.RECORDDATE) AS WD
FROM TEST.T_WEATHER A
) T
WHERE T.WD > T.TEMPERATURE
AND DATEDIFF(T.DD, T.RECORDDATE) = 1
;
|
报错
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
[执行语句1]:
SELECT T.*
FROM (
SELECT A.ID,
A.RECORDDATE,
A.TEMPERATURE,
LEAD(A.RECORDDATE, 1, NULL) OVER(ORDER BY A.RECORDDATE) AS DD,
LEAD(A.TEMPERATURE, 1, NULL) OVER(ORDER BY A.RECORDDATE) AS WD
FROM TEST.T_WEATHER A
) T
WHERE T.WD > T.TEMPERATURE
AND DATEDIFF(T.DD, T.RECORDDATE) = 1
;
执行失败(语句1)
-2007: 第 12 行, 第 26 列[T]附近出现错误:
语法分析出错
|
改造后的处理逻辑
1
2
3
4
5
6
7
8
9
10
11
12
|
SELECT T.*
FROM (
SELECT A.ID,
A.RECORDDATE,
A.TEMPERATURE,
LEAD(A.RECORDDATE, 1, NULL) OVER(ORDER BY A.RECORDDATE) AS DD,
LEAD(A.TEMPERATURE, 1, NULL) OVER(ORDER BY A.RECORDDATE) AS WD
FROM TEST.T_WEATHER A
) T
WHERE T.WD > T.TEMPERATURE
AND TRUNC(T.DD - 1) = TRUNC(T.RECORDDATE)
;
|
正常执行
附：TRUNC 用法
截取日期
1
2
3
4
5
6
7
|
SELECT TRUNC(A.RECORDDATE, 'YYYY') FROM TEST.T_WEATHER A; -- 返回当年第一天
SELECT TRUNC(A.RECORDDATE, 'MM') FROM TEST.T_WEATHER A; -- 返回当月第一天
SELECT TRUNC(A.RECORDDATE, 'DD') FROM TEST.T_WEATHER A; -- 返回当前年月日，缺省值 DD
SELECT TRUNC(A.RECORDDATE, 'HH') FROM TEST.T_WEATHER A; -- 返回当前小时
SELECT TRUNC(A.RECORDDATE, 'MI') FROM TEST.T_WEATHER A; -- 返回当前时间，没有秒
SELECT TRUNC(A.RECORDDATE, 'D') FROM TEST.T_WEATHER A; -- 返回当前星期的第一天（星期天为第一天）
|
日期计算
1
2
3
4
|
-- 在当前日期的基础上，增加一天（减少就 - ）
SELECT TRUNC(A.RECORDDATE, 'YYYY') + 1 FROM TEST.T_WEATHER A;
SELECT TRUNC(A.RECORDDATE, 'MM') + 1 FROM TEST.T_WEATHER A;
SELECT TRUNC(A.RECORDDATE, 'DD') + 1 FROM TEST.T_WEATHER A;
|
解决方法二
在 DTS 中使用数据类型映射，此方法为迁移时使用。或者在使用 DTS 工具刷入 SQL 脚本时使用。
