oracle 两个日期之间的星期五数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4967808/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Number of fridays between two dates
提问by ssahu
How do I find the number of fridays between two dates(including both the dates) using a select statement in oracle sql?
如何使用oracle sql中的select语句找到两个日期（包括两个日期）之间的星期五数？
回答by Tony Andrews
This will do it:
这将做到：
select ((next_day(date2-7,'FRI')-next_day(date-1,'FRI'))/7)+1 as num_fridays
from data
Perhaps best if I break that down. The NEXT_DAY function returns the next day that is a (Friday in this case) afterthe date.
如果我分解它，也许最好。该NEXT_DAY函数返回的第二天，也就是一个（星期五在这种情况下）后的日期。
So to find the first Friday after d1 would be:
因此，要找到 d1 之后的第一个星期五将是：
next_day( d1, 'FRI')
But if d1 is a Friday that would return the following Friday, so we adjust:
但是如果 d1 是一个星期五，它将返回下一个星期五，所以我们调整：
next_day( d1-1, 'FRI')
Similarly to find the last Friday up to and including d2 we do:
类似地，要找到直到并包括 d2 的最后一个星期五，我们这样做：
next_day( d1-7, 'FRI')
Subtracting the 2 gives a number of days: 0 if they are the same date, 7 if they a re a week apart and so on:
减去 2 给出天数：0 如果它们是相同的日期，7 如果它们相隔一周，依此类推：
next_day( d1-7, 'FRI') - next_day( d1-1, 'FRI')
Convert to weeks:
转换为周：
(next_day( d1-7, 'FRI') - next_day( d1-1, 'FRI')) / 7
Finally, if they are the same date we get 0, but really there is 1 Friday, and so on so we add one:
最后，如果它们是相同的日期，我们得到 0，但实际上有 1 个星期五，依此类推，所以我们加一个：
((next_day( d1-7, 'FRI') - next_day( d1-1, 'FRI')) / 7) + 1
回答by Mike Sherrill 'Cat Recall'
I have to throw in my two cents for using a calendar table. (It's a compulsion.)
我必须投入我的两分钱才能使用日历表。（这是一种强迫症。）
select count(*) as num_fridays
from calendar
where day_of_week = 'Fri'
and cal_date between '2011-01-01' and '2011-02-17';
num_fridays
-----------
6
Dead simple to understand. Takes advantage of indexes.
死的简单易懂。利用索引。
Maybe I should start a 12-step group. Calendar Table Anonymous.
也许我应该开始一个 12 步小组。日历表匿名。
回答by onedaywhen
See:
看：
Why should I consider using an auxiliary calendar table?
The article's code is specifically for SQL Server but the techniques are portable to most SQL platforms.
本文的代码专门用于 SQL Server，但这些技术可移植到大多数 SQL 平台。
With a Calendar table in place your query could be as simple as
有了日历表，您的查询就很简单了
SELECT COUNT(*) AS friday_tally
FROM YourTable AS T1
INNER JOIN Calendar AS C1
ON C1.dt BETWEEN T1.start_date AND T1.end_date
WHERE C1.day_name = 'Friday'; -- could be a numeric code
回答by Sachin Shanbhag
select sum(case when trim(to_char(to_date('2009-01-01','YYYY-MM-DD')+rownum,'Day')) = 'Friday' then 1 else 0 end) number_of_fridays
from dual
connect by level <= to_date('&end_date','YYYY-MM-DD') - to_date('&start_date','YYYY-MM-DD')+1;
Original source - http://forums.oracle.com/forums/thread.jspa?messageID=3987357&tstart=0
原始来源 - http://forums.oracle.com/forums/thread.jspa?messageID=3987357&tstart=0
回答by indra
Try modifying this one:
尝试修改这个：
CREATE OR REPLACE FUNCTION F_WORKINGS_DAYS
(V_START_DATE IN DATE, V_END_DATE IN DATE)
RETURN NUMBER IS
DAY_COUNT NUMBER := 0;
CURR_DATE DATE;
BEGIN -- loop through and update
CURR_DATE := V_START_DATE;
WHILE CURR_DATE <= V_END_DATE
LOOP
IF TO_CHAR(CURR_DATE,'DY') NOT IN ('SAT','SUN') -- Change this bit to ignore all but Fridays
THEN DAY_COUNT := DAY_COUNT + 1;
END IF;
CURR_DATE := CURR_DATE + 1;
END LOOP;
RETURN DAY_COUNT;
END F_WORKINGS_DAYS;
/
/
回答by Ram
SELECT (NEXT_DAY('31-MAY-2012','SUN')
-NEXT_DAY('04-MAR-2012','SUN'))/7 FROM DUAL
回答by user1672287
select ((DATEDIFF(dd,@a,@b)) + DATEPART(dw,(@a-6)))/7
选择 ((DATEDIFF(dd,@a,@b)) + DATEPART(dw,(@a-6)))/7
