Oracle SQL：返回输入分钟和最大日期字段的不同天数

发布于 2025-02-13 21:11:39 字数 418 浏览 0 评论 0原文

是以下正确的方法来获得日期范围（日期字段的最小和最大）的不同天数的正确方法，我打算从中创建一个SQL视图：

with range as (
  select min(date) start_date,
       max(date) end_date
  from table
)
  select start_date + level - 1 AS "DATE",
  extract(month from start_date + level - 1) AS "MONTH",
  extract(year from start_date + level - 1) AS "YEAR"
  from range
  connect by level <= (
     trunc(end_date) - trunc(start_date) + 1
  );

原文

Is the following the correct way to get a distinct list of days for a date range (min and max of a date field) I intend to create a sql view out of this:

with range as (
  select min(date) start_date,
       max(date) end_date
  from table
)
  select start_date + level - 1 AS "DATE",
  extract(month from start_date + level - 1) AS "MONTH",
  extract(year from start_date + level - 1) AS "YEAR"
  from range
  connect by level <= (
     trunc(end_date) - trunc(start_date) + 1
  );

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

岁月蹉跎了容颜 2025-02-20 21:11:39

如果需要时，您真的需要创建一个日期表


ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';


CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
/

CREATE OR REPLACE FUNCTION generate_dates_pipelined(
  p_from IN DATE,
  p_to   IN DATE
)
  RETURN nt_date PIPELINED DETERMINISTIC
IS
  v_start DATE := TRUNC(LEAST(p_from, p_to));
  v_end   DATE := TRUNC(GREATEST(p_from, p_to));
BEGIN
  LOOP
    PIPE ROW (v_start);
    EXIT WHEN v_start >= v_end;
    v_start := v_start + INTERVAL '1'DAY;
  END LOOP;
  RETURN;
END generate_dates_pipelined;
/

SELECT
        c.COLUMN_VALUE 
  FROM   
TABLE(generate_dates_pipelined(DATE '2022-07-01',
DATE '2022-07-31')) c

Do you really need to create a DATE table when you can generate one on the fly if needed


ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';


CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
/

CREATE OR REPLACE FUNCTION generate_dates_pipelined(
  p_from IN DATE,
  p_to   IN DATE
)
  RETURN nt_date PIPELINED DETERMINISTIC
IS
  v_start DATE := TRUNC(LEAST(p_from, p_to));
  v_end   DATE := TRUNC(GREATEST(p_from, p_to));
BEGIN
  LOOP
    PIPE ROW (v_start);
    EXIT WHEN v_start >= v_end;
    v_start := v_start + INTERVAL '1'DAY;
  END LOOP;
  RETURN;
END generate_dates_pipelined;
/

SELECT
        c.COLUMN_VALUE 
  FROM   
TABLE(generate_dates_pipelined(DATE '2022-07-01',
DATE '2022-07-31')) c

回复收藏 0 原文

~没有更多了~