【达梦数据库】mysql函数改写达梦

yearweek 改写为 year(‘2019-07-11’)||WEEK(‘2019-07-11’,1);

mysql> select YEARWEEK('2019-07-11',1);
+--------------------------+
| YEARWEEK('2019-07-11',1) |
+--------------------------+
|                   201928 |
+--------------------------+
1 row in set (0.08 sec)

mysql> 

1
2
3
4
5
6
7
8
9
10

SQL> select year('2019-07-11')||WEEK('2019-07-11',1);

LINEID     YEAR('2019-07-11')||WEEK('2019-07-11',1)
---------- ----------------------------------------
1          201928


1
2
3
4
5
6
7

select DATE_SUB(NOW(), INTERVAL 1 YEAR) 换成 dateadd(year,-1,NOW())

mysql> select DATE_SUB(NOW(), INTERVAL 1 YEAR);
+----------------------------------+
| DATE_SUB(NOW(), INTERVAL 1 YEAR) |
+----------------------------------+
| 2022-10-18 18:00:24              |
+----------------------------------+
1 row in set (0.11 sec)


1
2
3
4
5
6
7
8
9

SQL> select dateadd(year,-1,NOW());

LINEID     DATEADD(YEAR,-1,NOW())    
---------- --------------------------
1          2022-10-18 18:01:08.981492

1
2
3
4
5
6

PERIOD_DIFF改写为 moths_between

mysql> sELECT PERIOD_DIFF(202101,202001);
+----------------------------+
| PERIOD_DIFF(202101,202001) |
+----------------------------+
|                         12 |
+----------------------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8

SQL> sELECT months_between(to_date(202101,'yyyymm'),to_date(202001,'yyyymm'));

LINEID     MONTHS_BETWEEN(TO_DATE(202101,'yyyymm'),TO_DATE(202001,'yyyymm'))
---------- -----------------------------------------------------------------
1          12

used time: 0.220(ms). Execute id is 704.
SQL> sELECT DATEDIFF(month,to_date(202101,'yyyymm'),to_date(202001,'yyyymm'));

LINEID     DATEDIFF(MONTH,TO_DATE(202101,'yyyymm'),TO_DATE(202001,'yyyymm'))
---------- -----------------------------------------------------------------
1          -12

used time: 0.197(ms). Execute id is 705.
SQL> sELECT DATEDIFF(month,to_date(202001,'yyyymm'),to_date(202101,'yyyymm'));

LINEID     DATEDIFF(MONTH,TO_DATE(202001,'yyyymm'),TO_DATE(202101,'yyyymm'))
---------- -----------------------------------------------------------------
1          12

used time: 0.732(ms). Execute id is 706.

-- 达梦需要转换为时间类型来进行运算
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

持续收集中~~~~~