如果您通过了日期，如何获得 NextDayofWeek? [英] How to get NextDayofWeek if you pass the date?

问题描述

这就是我想要做的.我将以下代码更改为带有两个参数@startdate 和@transactionDate 的SP，它将返回NextTransactiondate.逻辑是@startdate 确定它是一周中的哪一天.@NexttransactionDate 应该等于交易日期的第二天.所以在这个例子中，开始日是星期三，所以下一个交易日期应该是 - 2011-05-04'.在下面的代码中，它总是计算到星期五，但它应该是根据日期动态计算的.任何帮助表示赞赏?

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

推荐答案

以下对我有用:

declare @TransactionDate DATETIME
DECLARE @TransactionDay tinyint
declare @startDate datetime
declare @startDay int
declare @NextTransactionDate datetime
--Monday
set @TransactionDate = '2011-05-05'
SET @TransactionDay = DATEPART(dw, @TransactionDate)
--Wednesday
set @startDate = '2011-04-27'
set @startDay = datepart(dw,@startDate)



set @NextTransactionDate= DATEADD(DAY, ((@startDay - @TransactionDay) + 7) % 7 ,@TransactionDate);  

select @startDay, DATEPART(dw, @NextTransactionDate), @NextTransactionDate

为了解释它的本质，我发现了 startDate 和 transactionDate 的星期几不同.我给它加了 14，因为负数模正数会导致负数，这会使您的下一个交易日期成为过去(而您不希望那样).最坏的情况是@startDay 为 1 且 @TransactionDay 为 7，这导致差值为 -6.添加 7 可确保该差异为正，但仍与环 n mod 7 中的实际差异处于相同的等价类中(抱歉……我有点数学书呆子).

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).

这篇关于如果您通过了日期，如何获得 NextDayofWeek?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！