T-SQL 如何获取 2 周支付期的日期范围 [英] T-SQL how to get date range for 2 week pay period

问题描述

我有以下种子输入数据:

I have following seed input data:

1. 发薪期开始:12 月 3 日
2. 支付期结束:12 月 16 日

这只是公司工资单的例子.

Thats just example how company payroll goes.

现在，我只有 1 个日期输入，例如 12/30(如今天)我需要输出当前付款期的日期范围，即:12 月 17 日至 12 月 30 日

Now, I have just 1 date input, for example 12/30 (as today) I need to output date range for current pay period which will be: Dec 17 to Dec 30

而且，如果我输入 Jan 3，同样的事情 - 我应该回到 12 月 31 日到 2012 年 1 月 13 日

And, same thing if I input Jan 3 - I should get back Dec 31 to Jan 13 2012

T-SQL 中是否有任何快捷方式或我必须执行循环?

Is there any shortcut in T-SQL or I have to do loops?

总结问题.如果我们知道支付周期何时开始(过去) - 我如何计算给定日期的支付周期开始时间?

To summarize question. if we know when pay cycle starts (in past) - how do I figure pay period start for given date?

推荐答案

您需要一些模运算和 DATEDIFF.

You need some modulo operations and DATEDIFF.

declare @periodStart datetime
declare @periodEnd datetime

set @periodStart = CAST('2011-12-03' as datetime)
set @periodEnd = CAST('2011-12-16' as datetime)

declare @anyDate datetime
set @anyDate = CAST('2011-12-30' as datetime)

declare @periodLength int
set @periodLength = DATEDIFF(day, @periodStart, @periodEnd) + 1


declare @daysFromFirstPeriod int
set @daysFromFirstPeriod = DATEDIFF(day, @periodStart, @anyDate)
declare @daysIntoPeriod int
set @daysIntoPeriod = @daysFromFirstPeriod % @periodLength

select @periodLength as periodLength, @daysFromFirstPeriod as daysFromFirstPeriod, @daysIntoPeriod as daysIntoPeriod
select DATEADD(day, -@daysIntoPeriod, @anyDate) as currentPeriodStart, DATEADD(day, @periodLength -@daysIntoPeriod, @anyDate) as currentPeriodEnd

给出输出

periodLength    daysFromFirstPeriod daysIntoPeriod
14              27                  13

和

currentPeriodStart        currentPeriodEnd
2011-12-17 00:00:00.000   2011-12-31 00:00:00.000

这篇关于T-SQL 如何获取 2 周支付期的日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！