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'
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: my expected result as follows:
when I enter 01/01/2017, then it will automatically generates every 15th day.
Example:
@StartYear: 01/01/2017
Result should be:
DatesBiweeklyPerMonthInAYear
-------------------------------
01/13/2017 (since the 15th day falls on sunday)
01/30/2017
02/15/2017
02/28/2017 (since no 30th day)
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.
Is this achievable? Or not?
Thanks!
your business rule is not 100% clear.
I think there can be more than one correct result.
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