两个日期之间的星期五数 [英] Number of fridays between two dates
问题描述
如何使用oracle sql中的select语句查找两个日期之间的星期五(包括两个日期)?
How do I find the number of fridays between two dates(including both the dates) using a select statement in oracle sql?
推荐答案
这样做:
select ((next_day(date2-7,'FRI')-next_day(date-1,'FRI'))/7)+1 as num_fridays
from data
最好的,如果我打破这一点。 NEXT_DAY函数返回第二天,这是一个(在这种情况下的星期五)之后的日期。
Perhaps best if I break that down. The NEXT_DAY function returns the next day that is a (Friday in this case) after the date.
所以要找到第一个星期五之后d1将是:
So to find the first Friday after d1 would be:
next_day( d1, 'FRI')
但是,如果d1是星期五,将返回下一个星期五,所以我们调整:
But if d1 is a Friday that would return the following Friday, so we adjust:
next_day( d1-1, 'FRI')
同样找到上周五直到并包括d2我们做:
Similarly to find the last Friday up to and including d2 we do:
next_day( d1-7, 'FRI')
减去2给出了几天:如果它们是相同的日期,则为0,如果它们相差一周,等等:
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:
next_day( d1-7, 'FRI') - next_day( d1-1, 'FRI')
转换为几周:
(next_day( d1-7, 'FRI') - next_day( d1-1, 'FRI')) / 7
最后,如果他们是同一个日期,我们得到0,但真的有一个星期五,所以我们添加一个:
Finally, if they are the same date we get 0, but really there is 1 Friday, and so on so we add one:
((next_day( d1-7, 'FRI') - next_day( d1-1, 'FRI')) / 7) + 1
这篇关于两个日期之间的星期五数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!