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屋！