如何在 SQL Server 中获得一年中每个月的双周日期?

问题描述：

当我设置开始年份和结束年份时，我试图生成每两周一次的日期.

I am trying to generate biweekly dates when I set a start year and end year.

DECLARE @StartYear DATETIME
DECLARE @EndYear DATETIME

SET @StartYear = '01/01/2017'
SET @EndYear = '12/31/2017'

T-SQL 然后应该计算每两周的日期，例如:03/15/2017 和 03/30/2017 是发薪日.

The T-SQL should then compute the biweekly dates, example: 03/15/2017 and 03/30/2017 are the paydays.

此外，如果日期落在星期六和星期日，那么它会生成到其第一个星期五的日期.

Also if the dates fall on Saturday and Sunday, then it will generate the dates to its 1st Friday.

希望有人能帮我解决这个问题.或者有人想分享他/她关于这方面的知识和公式.

Hope someone could help me with this. Or someone would like to share his/her knowledge and formula about this.

UPDATE:我的预期结果如下:

当我输入 01/01/2017 时，它会每 15 天自动生成一次.

when I enter 01/01/2017, then it will automatically generates every 15th day.

示例:

@StartYear: 01/01/2017

结果应该是:

DatesBiweeklyPerMonthInAYear
-------------------------------
01/13/2017 (since the 15th day falls on sunday)
01/30/2017
02/15/2017
02/28/2017 (since no 30th day)

如果第 15 天落在 sun 或 sat 上，则它将落在该特定周的星期五.依此类推……直到年底.

If 15th day falls on sun or sat it will fall on Friday of that specific week. so on... until the end of the year.

这能实现吗?或不?

谢谢！

答

您的业务规则并非 100% 明确.

your business rule is not 100% clear.

我认为正确的结果可能不止一个.

I think there can be more than one correct result.

在一个地方，它是硬编码的，因为我想 100% 确定需求.我没有使用游标.它只有 24 个循环.

In one place it is hard coded,because i want to be 100% sure of requirement. I am not using cursor.Its only 24 loops.

declare @StartYear datetime='2017-01-01'
declare @endYear datetime ='2017-12-31'

declare @gap int =14 --Bimonthly means gap of 14 days or 15 days whatever

;With CTE as
(
select dateadd(day,@gap, @StartYear) Bimonthly
,1 rn

UNION ALL

select  
case 

when (rn+1)%2=0 and datename(m, Bimonthly)='February' THEN
      '2017-02-28'
      when (rn+1)%2=0 and datename(m, Bimonthly)!='February' 

then dateadd(day,@gap, Bimonthly)
else 
dateadd(day,@gap, dateadd(month, datediff(month,0,dateadd(month,1,Bimonthly)),0))
END
,rn+1
from cte
where rn< (datediff(month,@StartYear, @endYear)+1)*2

)
select 
case WHEN datename(dw, Bimonthly)='Saturday' THEN
      dateadd(day,-1, Bimonthly) 
      WHEN datename(dw, Bimonthly)='Sunday' THEN 
      dateadd(day,-2, Bimonthly) 
     else 
      Bimonthly
     end 

     ,rn

from cte

如何在 SQL Server 中获得一年中每个月的双周日期?

相关推荐