如果过了日期如何获取NextDayofWeek?
这就是我正在尝试做的事情。我将以下代码更改为 SP,它接受两个参数 @startdate 和 @transactionDate,它将返回 NextTransactiondate。逻辑是 @startdate 确定它是一周中的哪一天。 @NexttransactionDate 应等于交易日期的第二天。因此,在此示例中,开始日是星期三,因此下一个交易日期应该是 - 2011-05-04'。在下面的代码中,它总是计算到星期五,但它应该根据日期动态计算。有什么帮助值得赞赏吗?
declare @TransactionDate datetime
declare @startDate datetime
declare @startDay int
declare @NextTransactionDate datetime
--Monday
set @TransactionDate = '2011-05-02'
--Wednesday
set @startDate = '2011-04-27'
set @startDay = datepart(dw,@startDate)
set @NextTransactionDate= DATEADD(DAY,(CASE DATEPART(DW,@TransactionDate)
WHEN 7 THEN 6
WHEN 6 THEN 7
ELSE 6 - DATEPART(DW,@TransactionDate)
END),@TransactionDate);
print @NextTransactionDate
Here's what I am trying to do. I will change the following code into SP which takes two parameter @startdate, and @transactionDate, and it will return the NextTransactiondate. The logic is @startdate determine which day of the week it is. The @NexttransactionDate should be equal to the day following the transactiondate. so in this example, the startday is Wednesday so the next transaction date should be - 2011-05-04'. In the code below, it is always computing to friday, but it should be dynamically compute based on the day. Any help is appreciated?
declare @TransactionDate datetime
declare @startDate datetime
declare @startDay int
declare @NextTransactionDate datetime
--Monday
set @TransactionDate = '2011-05-02'
--Wednesday
set @startDate = '2011-04-27'
set @startDay = datepart(dw,@startDate)
set @NextTransactionDate= DATEADD(DAY,(CASE DATEPART(DW,@TransactionDate)
WHEN 7 THEN 6
WHEN 6 THEN 7
ELSE 6 - DATEPART(DW,@TransactionDate)
END),@TransactionDate);
print @NextTransactionDate
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
以下内容对我有用:
为了解释它的实质,我发现了 startDate 和 transactionDate 的星期几的差异。我在其中添加 14,因为负数模正数会得到负数,这会使您的下一个交易日期成为过去(您不希望这样)。最坏的情况是当@startDay 为1 而@TransactionDay 为7 时,会导致-6 的差异。添加 7 可确保该差异为正,但仍与环 n mod 7 中的实际差异处于相同的等价类(抱歉...我有点数学呆子)。
The following works for me:
To explain the meat of it, I'm finding the difference in the day-of-week for the startDate and the transactionDate. I add 14 to it because negative numbers modulo positive numbers result in a negative number, which would put your next transaction date in the past (and you don't want that). The worst case is when @startDay is 1 and @TransactionDay is 7 which leads to a difference of -6. Adding 7 ensures that that difference is positive but still in the same equivalence class as the actual difference in the ring n mod 7(sorry... I'm a bit of a math nerd).
试试这个:
Try this:
我不确定我是否听懂了你所说的。我认为您是说下一个 @TransactionDate 应该是 @startDate 所在的一周中的下一次出现的日期。
如果是这种情况,您可以尝试:
我不确定您对 6 和 7 做了什么...您是否想确保新的 @TransactionDate 不是周末?如果是这样,则需要稍微修改一下......
I'm not sure I follow what you're saying. I think you're saying that the next @TransactionDate, should be the next occurence of the day of the week that the @startDate falls on.
If that's the case, you could try :
I'm not sure what you're doing with the 6 and 7 though... are you trying to make sure the new @TransactionDate is not a weekend? If so, this would need to be slightly modified...