两个日期范围之间相等的天数,SQL [英] How many equal days are between two date ranges, SQL
问题描述
我有一个带有日期范围的表,像这样:
I have table with date ranges, like this:
DATE DATE2
14.03.2013 17.03.2013
13.04.2013 02.05.2013
我必须创建一个过程,该过程返回等于两个日期范围的天数,一个在表中,另一个在表中.
I have to create a procedure, that returns day count that is equal to two date ranges, one which is in the table and another one.
例如,我在表格中有这样的日期范围,如14.03.2013-17.03.2013,而另一个则在此程序中声明,例如02.03.2013-16.03.2013,因此在这种情况下,天数为3因为两个日期范围的日期都在2013年3月14日至2013年3月16日之间.
Forexample, I have date range in the table like this 14.03.2013 - 17.03.2013 and another one, which is declared in procedure like this 02.03.2013 - 16.03.2013, so in this case day count would be 3, because, both date ranges have dates between 14.03.2013 and 16.03.2013.
推荐答案
假设您的表名为daterange,并且您在过程中定义了@ param1和param2参数,那么这些行上的内容应该可以工作:
suppose your table is called daterange and you have parameters defined @param1 and param2 in your procedure then something on these lines should work:
set @param1 := cast('2013-03-14' as date);
set @param2 := cast('2013-03-16' as date);
select
datediff(least(date2,@param2),@param1)+1
from daterange where @param1 between date1 and date2
请参见 sqlfiddle
这篇关于两个日期范围之间相等的天数,SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!