Sql Oracle 23c ROUND，数据类型为DATE

ROUND(date_value, 'DAY')舍入到最接近的一周开始(由NLS_TERRITORY会话/数据库参数定义).

ROUND(date_value, 'DD')是四舍五入到最接近的日子.

这一点记录在ROUND and TRUNC Date Functions documentation:

Format Model	Rounding or Truncating Unit
DDD DD J	Day
DAY DY D	Starting day of the week

例如:

如果您有样例数据:

CREATE TABLE table_name (dt) AS
SELECT TRUNC(DATE '2024-06-26', 'IW') + LEVEL - 1 + INTERVAL '15:02:18' HOUR TO SECOND
FROM   DUAL
CONNECT BY LEVEL <= 7;

并且您可以使用:

ALTER SESSION SET NLS_TERRITORY = 'Germany'
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS (DY)';
SELECT dt,
       ROUND(dt)        AS default_result,
       ROUND(dt, 'DAY') AS day_explicit,
       ROUND(dt, 'DD') AS dd_explicit
FROM   table_name;

则输出为:

因为在欧洲大部分地区，一周的开始是星期一.

如果您在不同地区使用相同的查询:

ALTER SESSION SET NLS_TERRITORY = 'America'
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS (DY)';
SELECT dt,
       ROUND(dt)        AS default_result,
       ROUND(dt, 'DAY') AS day_explicit,
       ROUND(dt, 'DD') AS dd_explicit
FROM   table_name;

则输出为:

因为甲骨文认为美国一周的开始是周日.

如果您这样做了:

ALTER SESSION SET NLS_TERRITORY = 'Bangladesh'
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS (DY)';
SELECT dt,
       ROUND(dt)        AS default_result,
       ROUND(dt, 'DAY') AS day_explicit,
       ROUND(dt, 'DD') AS dd_explicit
FROM   table_name;

则输出为:

因为孟加拉的这周是星期五开始.

同样，如果你把NLS_TERRITORY设定为中东的一个国家，你会发现这一周大多是从周六开始的.

Note: If you want to always round to Monday then use the 100 format model for the start of the ISO week (as defined by ISO 8601).

fiddle个