count sql中位于date1和:date2之间的Sunday

Question

我知道在论坛上有很多工作，但我尝试了很多东西，但得到错误，请我在oracle报告中有两个参数:date1和:date2我想检查星期日，然后返回给我在这两个日期中有多少个星期日

function SUNDAY_CFormula return NUMBER is
start_date DATE := :DATE1;
end_date DATE := :DATE2;
A NUMBER;
begin
SELECT Count(*) 
FROM   (SELECT To_char(start_date + ( LEVEL - 1 ), 'fmday')INTO A 
FROM DUAL;   
CONNECT BY LEVEL <= end_date - start_date + 1) 
WHERE  A IN ( 'sunday' );
RETURN A;
end;

Answer 1

您可以像下面这样重写函数。在你的to_char函数中添加'nls_date_language = english'子句会更安全，这样可以使你的函数独立于默认环境设置。

create or replace 
function SUNDAY_CFormula (DATE1 date, DATE2 date) return NUMBER is
start_date DATE := DATE1;
end_date DATE := DATE2;
A NUMBER;
begin
SELECT Count(*) INTO A
FROM   (
SELECT To_char(start_date + ( LEVEL - 1 ), 'fmday', 'nls_date_language = english') A
FROM DUAL 
CONNECT BY LEVEL <= end_date - start_date + 1
) t
WHERE  t.A IN ( 'sunday' );
RETURN A;
end;
/

你甚至可以使用下面的版本，使你的函数更灵活的两个日期作为参数，无论date1是大于还是小于date2。

create or replace 
function SUNDAY_CFormula (DATE1 date, DATE2 date) return NUMBER is
start_date DATE := DATE1;
end_date DATE := DATE2;
A NUMBER;
begin
SELECT Count(*) INTO A
FROM   (SELECT To_char(start_date + ( LEVEL - 1 ), 'fmday', 'nls_date_language = english') A
FROM DUAL 
CONNECT BY LEVEL <= greatest(end_date, start_date) - least(end_date, start_date) + 1
) t
WHERE  t.A IN ( 'sunday' );
RETURN A;
end;
/

Answer 2

作为替代。我总是尝试为日期范围过程创建公式，而不是"迭代"，我只是不喜欢生成数据就把它扔掉。是的，有时是必要的，但不是在这种情况下。下面的代码将完成你想要的:

create or replace 
function sunday_calc ( date1_in  date
, date2_in  date 
, sun_in    varchar2 default 'sun'
) 
return number 
is
sun_count integer; 
begin
with date_range( start_date, end_date) as
( select trunc(least(date1_in,date2_in))
, trunc(greatest(date1_in,date2_in))
from dual
) 
select floor((trunc(end_date) - trunc(next_day(start_date-1,sun_in))/7)) + 1
into sun_count 
from date_range;
return sun_count;
end sunday_calc;

注意:不幸的是，next_day函数不接受NLS_DATE_LANGUAGE参数，所以我创建了一个替代。Sun_in参数:包含与英语日"Sunday"对应的目标语言名称

---

对@MDO的函数进行比较感到好奇，我对每个函数都进行了一些测试。他们得出了相同的结果;除了在某些情况下，如果开始日期大于结束日期，则差异为1。与实际日历相比，这个公式是正确的。但为什么，MDO的逻辑似乎完全合理。那时候我只想知道为什么。花了一段时间，但她/他的代码中有一个小错误。事实证明，当开始日期比结束日期大时，他们的日常程序实际上开始寻找最大的日期并向前移动。因此，更改周期查看日期中较大的日期为该日期加上天数。通过对"Select to_char(start_date…&"应用最小函数来纠正这一点，结果为:

create or replace 
function sunday_cformula_r (date1 date, date2 date) return number is
start_date date := date1;
end_date date := date2;
a number;
begin
select count(*) into a
from   (select to_char(least(end_date, start_date) + ( level - 1 ), 'fmday', 'nls_date_language = english') a
from dual 
connect by level <= greatest(end_date, start_date) - least(end_date, start_date) + 1

) t
where  t.a in ( 'sunday' );
return a;
end;