SQL Server:计算双周支付期日期 [英] SQL Server : calculate Bi-Weekly Pay Period Dates
问题描述
我有以下内容来获得双周支付期的日期,这周从周六开始当我假设今天的日期是 02/22/2016 运行它时,我得到以下内容
I have the following to get bi-weekly pay period dates, The week starts on Sat When I run it assuming today's date is 02/22/2016 I get the following
- 支付期开始:02/13/2016
- 支付期结束:02/23/2016
但实际上应该是 02/20/2016 - 3/4/2016 .好像休息了一个星期.如果我从星期一开始一周,那么一切正常.
But it should actually be 02/20/2016 - 3/4/2016 . It seems to be a week off. If I start the week on Monday, then everything works fine.
我做错了什么,任何帮助将不胜感激.
What am I doing wrong, any help would be appreciated.
CREATE FUNCTION dbo.getFirstWeekDayDate (
@TargetDay DATETIME,
@strWeekDayName VARCHAR(25) --Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday
)
RETURNS DATE
AS
BEGIN
DECLARE @Return DATE
-- Set to first of its year
SET @TargetDay = DATEADD(dd, -DATEPART(dayofyear, @TargetDay) + 1, @TargetDay)
;WITH Dates AS
(
SELECT @TargetDay AS DateVal
UNION ALL
SELECT DATEADD(d, 1, DateVal) AS DateVal
FROM Dates
WHERE DATEADD(d, 1, DateVal) < DATEADD(m, 1, @TargetDay)
)
SELECT @Return = MIN(DateVal)
FROM Dates
WHERE DATENAME(WEEKDAY,DateVal) = @strWeekDayName
RETURN @Return
END
GO
DECLARE
@periodstart date, @period int, @today date
set @today = '02/22/2016'
set @periodstart = dbo.getFirstWeekDayDate(@today,'Saturday') --get first sat
set @period = datediff(dd,@periodstart,@today)/14
select
@period AS period,
dateadd(dd, @period * 14, @periodstart) AS [payPeriodStart],
dateadd(dd, @period * 14 + 13, @periodstart) AS [payPeriodEnd]
结果
period payPeriodStart payPeriodEnd
----------- -------------- ------------
3 2016-02-13 2016-02-26
推荐答案
好的.感谢您的所有评论.我决定让用户在设置中指定第一个支付期结束日期,然后我用它来计算剩余的支付期.
Ok. Thank you for all the comments. I decided to let the user specify first pay period end date in the settings and then I used that calculate the rest of the pay periods.
这篇关于SQL Server:计算双周支付期日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!