Oracle兼容-函数-TRUNC()函数

函数 TRUNC() 支持两种用法：

  • TRUNC(date [, fmt ])
  • TRUNC(n1 [, n2 ])

下面分别详细说明两种不同用法。

1. 语法1：TRUNC(date [, fmt ])

  1. TRUNC(date [, fmt ])

2. 定义和用法

函数 TRUNC(date [, fmt ]) 的作用是根据 fmt 指定的时间单元，对日期 date 进行截取。当不指定fmt时，日期被截断为指定日期的0点（等价于 TRUNC(DATE, 'DD')）。

注：参数 date 的数据类型必须为 DATE, DATETIME, TIMESTAMP 等类型，否则将按TRUNC(NUMBER) 用法处理， 如：

  1. -- TRUNC(DATE)
  2. greatsql> SELECT NOW(), TRUNC(NOW(), 'yyyy') AS trunc_year, TRUNC(NOW(), 'mm') AS trunc_month, TRUNC(NOW(), 'dd') AS trunc_day FROM DUAL\G
  3. *************************** 1. row ***************************
  4.       now(): 2023-05-18 14:21:23
  5.  trunc_year: 2023-01-01 00:00:00
  6. trunc_month: 2023-05-01 00:00:00
  7.   trunc_day: 2023-05-18 00:00:00
  9. -- 执行 TRUNC('2021-10-21', 'dd')时，字符串 '2021-10-21' 没有先转换成 DATE 类型，此时会被当做 TRUNC(NUMBER) 用法处理
  10. -- 在 TRUNC(NUMBER) 用法中，'2021-10-21' 是一个无效数字，从而提示参数错误
  11. greatsql> SELECT TRUNC('2021-10-21', 'dd') FROM DUAL;
  12. ERROR 1210 (HY000): Incorrect arguments to trunc
  14. -- 下面用法被当做 TRUNC(NUMBER, 0) 处理
  15. greatsql> SELECT TRUNC('20211021') FROM DUAL;
  16. +-------------------+
  17. | TRUNC('20211021') |
  18. +-------------------+
  19. |          20211021 |
  20. +-------------------+

截取日期时间用法中，参数 fmt 支持格式及对应日期截断说明：

格式说明
CC, SCC截断至日期所在世纪第一天
[S]YYYY, [S]YEAR, YYY, YY, Y截断至日期所在年的第一天
IYYY,IYY,IY,I截断至日期所在ISO年的第一天
Q截断至日期所在季第一天
MONTH, MON, MM, RM截断至日期所在月第一天
WW截断至与日期所在年第一天(1月1日)星期数相同的最近日期
IW截断至日期所在星期(ISO)第一天 (周一)
W往回截断至与日期所在月第一天(1日)星期数相同的最近日期
DDD,DD,J截断至当日凌晨 00:00:00
DAY,DY,D截断至日期所在星期第一天 (周日）
HH, HH12, HH24截断至日期所在小时起始(分、秒等归零)
MI截断至日期所在分钟起始(秒、微秒等归零)

3. 示例

  1. greatsql> SELECT TRUNC(TO_DATE('2003-01-02 10:11:12','yyyy-mm-dd hh:mi:ss'), 'YEAR') FROM DUAL;
  2. +---------------------------------------------------------------------+
  3. | TRUNC(TO_DATE('2003-01-02 10:11:12','yyyy-mm-dd hh:mi:ss'), 'YEAR') |
  4. +---------------------------------------------------------------------+
  5. | 2003-01-01 00:00:00                                                 |
  6. +---------------------------------------------------------------------+
  8. greatsql> SELECT TRUNC(TO_DATE('2003-01-02 8:11:2','yyyy-mm-dd hh:mi:ss'), 'MI') FROM DUAL;
  9. +-----------------------------------------------------------------+
  10. | TRUNC(TO_DATE('2003-01-02 8:11:2','yyyy-mm-dd hh:mi:ss'), 'MI') |
  11. +-----------------------------------------------------------------+
  12. | 2003-01-02 08:11:00                                             |
  13. +-----------------------------------------------------------------+
  15. greatsql> SELECT TRUNC(TO_DATE('2003-01-02 8:11:2','yyyy-mm-dd hh:mi:ss'), 'HH') FROM DUAL;
  16. +-----------------------------------------------------------------+
  17. | TRUNC(TO_DATE('2003-01-02 8:11:2','yyyy-mm-dd hh:mi:ss'), 'HH) |
  18. +-----------------------------------------------------------------+
  19. | 2003-01-02 08:00:00                                             |
  20. +-----------------------------------------------------------------+
  22. greatsql> SELECT TO_DATE('2008-11-10', 'yyyy-mm-dd'),
  23.  TRUNC(TO_DATE('2008-11-10', 'yyyy-mm-dd'), 'year'),
  24.  TRUNC(TO_DATE('2008-11-10', 'yyyy-mm-dd'), 'dd'),
  25.  TRUNC(TO_DATE('2008-11-10', 'yyyy-mm-dd')) FROM DUAL\G
  26. *************************** 1. row ***************************
  27.                TO_DATE('2008-11-10', 'yyyy-mm-dd'): 2008-11-10 00:00:00
  28. TRUNC(TO_DATE('2008-11-10', 'yyyy-mm-dd'), 'year'): 2008-01-01 00:00:00
  29.   TRUNC(TO_DATE('2008-11-10', 'yyyy-mm-dd'), 'dd'): 2008-11-10 00:00:00
  30.         TRUNC(TO_DATE('2008-11-10', 'yyyy-mm-dd')): 2008-11-10 00:00:00
  32. -- 输入日期年份或TRUNC计算后年份小于1时，TRUNC返回NULL，并提示日期溢出warning
  33. greatsql> SELECT TRUNC(DATE('0000-01-10'), 'ww') FROM DUAL;
  34. +---------------------------------+
  35. | TRUNC(DATE('0000-01-10'), 'ww') |
  36. +---------------------------------+
  37. | NULL                            |
  38. +---------------------------------+
  39. 1 row in set, 1 warning (0.01 sec)
  41. greatsql> show warnings;
  42. +---------+------+-----------------------------------------+
  43. | Level   | Code | Message                                 |
  44. +---------+------+-----------------------------------------+
  45. | Warning | 1441 | Datetime function: trunc field overflow |
  46. +---------+------+-----------------------------------------+
  48. greatsql> SELECT TRUNC(DATE('0001-01-02'), 'iy') FROM DUAL;
  49. +---------------------------------+
  50. | TRUNC(DATE('0001-01-02'), 'iy') |
  51. +---------------------------------+
  52. | NULL                            |
  53. +---------------------------------+
  54. 1 row in set, 1 warning (0.01 sec)
  56. greatsql> show warnings;
  57. +---------+------+-----------------------------------------+
  58. | Level   | Code | Message                                 |
  59. +---------+------+-----------------------------------------+
  60. | Warning | 1441 | Datetime function: trunc field overflow |
  61. +---------+------+-----------------------------------------+

4. 语法2：TRUNC(n1 [, n2 ])

  1. TRUNC(n1 [, n2 ])

5. 定义和用法

函数 TRUNC(n1 [, n2 ]) 的作用是根据 n2 指定的小数点位置，对数值 n1 进行截取。截取规则如下：

  • 当 n2 为正数时，截断 n1 小数点后 n2 位数值；
  • 当 n2 为0时，截断 n1 的小数部分数值；
  • 当 n2 为负数时，将 n1 小数点左边的 n2 个数值都重置为0；
  • 若 n2 未被指定，使用默认值0，即 TRUNC(n1, 0)。

6. Oracle兼容说明

说明示例Oracle返回值GreatSQL返回值
小数最大保留位数(Oracle 38位，GreatSQL 30位)TRUNC(1.123456789123456789123456789123456789123456789, 100)1.123456789123456789123456789123456789121.123456789123456789123456789123
小数点位置指定符对小数的处理方式(Oracle截断，GreatSQL四舍五入)TRUNC(123.456, 1.1)
TRUNC(123.456, 1.5)		123.4
123.4		123.4
123.45

7. 示例

  1. greatsql> SELECT TRUNC(123.456, 2) FROM DUAL;
  2. +-------------------+
  3. | TRUNC(123.456, 2) |
  4. +-------------------+
  5. |            123.45 |
  6. +-------------------+
  8. greatsql> SELECT TRUNC(123.456, -1) FROM DUAL;
  9. +-------------------+
  10. | TRUNC(123.456,-1) |
  11. +-------------------+
  12. |               120 |
  13. +-------------------+
  15. greatsql> SELECT TRUNC(123.456, -2) FROM DUAL;
  16. +--------------------+
  17. | TRUNC(123.456, -2) |
  18. +--------------------+
  19. |                100 |
  20. +--------------------+
  22. greatsql> SELECT TRUNC(123.456) FROM DUAL;
  23. +----------------+
  24. | TRUNC(123.456) |
  25. +----------------+
  26. |            123 |
  27. +----------------+
  29. greatsql> SELECT TRUNC(123.456, 'abc') FROM DUAL;
  30. ERROR 1210 (HY000): Incorrect arguments to trunc

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx