SQL兼容性 - TRUNC()函数

---

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

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

下面分别详细解释两种不同用法。

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

TRUNC(date [, fmt ])

2. 定义和用法

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

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

-- trunc(date)
greatsql> SELECT now(), trunc(now(), 'yyyy') as trunc_year, trunc(now(), 'mm') as trunc_month, trunc(now(), 'dd') as trunc_day from dual\G
*************************** 1. row ***************************
      now(): 2023-05-18 14:21:23
 trunc_year: 2023-01-01 00:00:00
trunc_month: 2023-05-01 00:00:00
  trunc_day: 2023-05-18 00:00:00

-- 执行 trunc('2021-10-21', 'dd')时，字符串 '2021-10-21' 没有先转换成 DATE 类型，此时会被当做 trunc(number) 用法处理
-- 在 trunc(number) 用法中，'2021-10-21' 是一个无效数字，从而提示参数错误
greatsql> SELECT trunc('2021-10-21', 'dd') from dual;
ERROR 1210 (HY000): Incorrect arguments to trunc

-- 下面用法被当做 trunc(number, 0) 处理
greatsql> SELECT trunc('20211021') from dual;
+-------------------+
| trunc('20211021') |
+-------------------+
|          20211021 |
+-------------------+

截取日期时间用法中，参数 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. 示例

greatsql> select trunc(to_date('2003-01-02 10:11:12','yyyy-mm-dd hh:mi:ss'), 'YEAR') from dual;
+---------------------------------------------------------------------+
| trunc(to_date('2003-01-02 10:11:12','yyyy-mm-dd hh:mi:ss'), 'YEAR') |
+---------------------------------------------------------------------+
| 2003-01-01 00:00:00                                                 |
+---------------------------------------------------------------------+

greatsql> select trunc(to_date('2003-01-02 8:11:2','yyyy-mm-dd hh:mi:ss'), 'MI') from dual;
+-----------------------------------------------------------------+
| trunc(to_date('2003-01-02 8:11:2','yyyy-mm-dd hh:mi:ss'), 'MI') |
+-----------------------------------------------------------------+
| 2003-01-02 08:11:00                                             |
+-----------------------------------------------------------------+

greatsql> select trunc(to_date('2003-01-02 8:11:2','yyyy-mm-dd hh:mi:ss'), 'HH') from dual;
+-----------------------------------------------------------------+
| trunc(to_date('2003-01-02 8:11:2','yyyy-mm-dd hh:mi:ss'), 'HH) |
+-----------------------------------------------------------------+
| 2003-01-02 08:00:00                                             |
+-----------------------------------------------------------------+

greatsql> select to_date('2008-11-10', 'yyyy-mm-dd') , trunc(to_date('2008-11-10', 'yyyy-mm-dd'), 'year') , trunc(to_date('2008-11-10', 'yyyy-mm-dd'), 'dd') , trunc(to_date('2008-11-10', 'yyyy-mm-dd')) from dual\G
*************************** 1. row ***************************
               to_date('2008-11-10', 'yyyy-mm-dd'): 2008-11-10 00:00:00
trunc(to_date('2008-11-10', 'yyyy-mm-dd'), 'year'): 2008-01-01 00:00:00
  trunc(to_date('2008-11-10', 'yyyy-mm-dd'), 'dd'): 2008-11-10 00:00:00
        trunc(to_date('2008-11-10', 'yyyy-mm-dd')): 2008-11-10 00:00:00

-- 输入日期年份或trunc计算后年份小于1时，trunc返回NULL，并提示日期溢出warning
greatsql> select trunc(date('0000-01-10'), 'ww') from dual;
+---------------------------------+
| trunc(date('0000-01-10'), 'ww') |
+---------------------------------+
| NULL                            |
+---------------------------------+
1 row in set, 1 warning (0.01 sec)

greatsql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1441 | Datetime function: trunc field overflow |
+---------+------+-----------------------------------------+

greatsql> select trunc(date('0001-01-02'), 'iy') from dual;
+---------------------------------+
| trunc(date('0001-01-02'), 'iy') |
+---------------------------------+
| NULL                            |
+---------------------------------+
1 row in set, 1 warning (0.01 sec)

greatsql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1441 | Datetime function: trunc field overflow |
+---------+------+-----------------------------------------+

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

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. SQL兼容说明

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

7. 示例

greatsql> SELECT TRUNC(123.456, 2) FROM DUAL;
+-------------------+
| TRUNC(123.456, 2) |
+-------------------+
|            123.45 |
+-------------------+

greatsql> SELECT TRUNC(123.456, -1) FROM DUAL;
+-------------------+
| TRUNC(123.456,-1) |
+-------------------+
|               120 |
+-------------------+

greatsql> SELECT TRUNC(123.456, -2) FROM DUAL;
+--------------------+
| TRUNC(123.456, -2) |
+--------------------+
|                100 |
+--------------------+

greatsql> SELECT TRUNC(123.456) FROM DUAL;
+----------------+
| TRUNC(123.456) |
+----------------+
|            123 |
+----------------+

greatsql> SELECT TRUNC(123.456, 'abc') FROM DUAL;
ERROR 1210 (HY000): Incorrect arguments to trunc

问题反馈

• 问题反馈 gitee (opens new window)

联系我们

扫码关注微信公众号