5.1.2 时间函数
可以在查询的 Projection 子句或 WHERE 子句中使用时间函数 DAY 、MONTH 、
WEEKDAY 和 YEAR 。这些函数返回与用来调用函数的表达式或参数对应的值。还可以使
用 CURRENT 或 SYSDATE 函数返回具有当前日期和时间的值,或者使用 EXTEND 函数调
整 DATE 或 DATETIME 值。
DAY 和 CURRENT 函数
下列查询在两个 expression 列中对 call_dtime 和 res_dtime 列返回日期(一个月中的某一
天)。
图: 查询
SELECT customer_num, DAY (call_dtime), DAY (res_dtime)
FROM cust_calls;
图: 查询结果
customer_num (expression) (expression)
106 12 12
110 7 7
119 1 2
121 10 10
127 31
116 28 28
116 21 27
下列查询使用 DAY 和 CURRENT 函数来将列值与当前日期(月中某日)进行比较。它只
选择值比当前日期早的那些行。在此示例中,CURRENT 日是 15。
图: 查询
SELECT customer_num, DAY (call_dtime), DAY (res_dtime)
FROM cust_calls
WHERE DAY (call_dtime) < DAY (CURRENT);
图: 查询结果
customer_num (expression) (expression)
106 12 12
110 7 7
119 1 2
121 10 10
下列查询使用 CURRENT 函数来选择除今天打的电话之外的所有来电。
图: 查询
SELECT customer_num, call_code, call_descr
FROM cust_calls
WHERE call_dtime < CURRENT YEAR TO DAY;
图: 查询结果
customer_num 106
call_code D
call_descr Order was received, but two of the cans of ANZ tennis
balls
within the case were empty
customer_num 110
call_code L
call_descr Order placed one month ago (6/7) not received.
⋮
customer_num 116
call_code I
call_descr Second complaint from this customer! Received two cases
right-handed outfielder gloves (1 HRO) instead of one case
lefties.
SYSDATE 函数与 CURRENT 函数及其类似,但当未指定 DATETIME 限定符时,其返回
值的缺省精度是 DATETIME YEAR TO FRACTION(5),而不是 CURRENT 的缺省精度
DATETIME YEAR TO FRACTION(3)。
GBase 8s V8.8 SQL 指南:教程-5.1.2(1)
最新推荐文章于 2022-09-17 22:27:20 发布