問題描述

當我設置開始年份和結束年份時，我試圖生成每兩周一次的日期.

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 是發(fā)薪日.

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.

這能實現(xiàn)嗎?或不?

謝謝！

推薦答案

您的業(yè)務規(guī)則并非 100% 明確.

your business rule is not 100% clear.

我認為正確的結果可能不止一個.

I think there can be more than one correct result.

在一個地方，它是硬編碼的，因為我想 100% 確定需求.我沒有使用游標.它只有 24 個循環(huán).

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 中獲得一年中每個月的雙周日期?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！