如何在 SQL Server 中获得一年中每个月的双周日期? [英] How to get biweekly dates per month in a year in 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 中获得一年中每个月的双周日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！