如果过了日期如何获取NextDayofWeek?

发布于 2024-11-09 03:18:37 字数 730 浏览 0 评论 0原文

这就是我正在尝试做的事情。我将以下代码更改为 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

拒绝两难 2024-11-16 03:18:37

以下内容对我有用:

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 中的实际差异处于相同的等价类(抱歉...我有点数学呆子)。

The following works for me:

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

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

安穩 2024-11-16 03:18:37

试试这个:


declare @TransactionDate datetime 
declare @startDate datetime 
declare @startDay int 
declare @transactionDay int 
declare @NextTransactionDate datetime 
declare @daysToAdd int

--Monday 
set @TransactionDate = '2011-05-02' 
set @transactionDay = datepart(dw,@TransactionDate)  

--Wednesday 
set @startDate = '2011-04-27' 
set @startDay = datepart(dw,@startDate)  

print @transactionDay
print @startDay

if(@startDay <= @transactionDay)
    set @daysToAdd = (@startDay + 7) - @transactionDay
else
   set @daysToAdd = @startDay - @transactionDay

set @NextTransactionDate = Dateadd(Day,@daysToAdd,@TransactionDate)
print @NextTransactionDate

Try this:


declare @TransactionDate datetime 
declare @startDate datetime 
declare @startDay int 
declare @transactionDay int 
declare @NextTransactionDate datetime 
declare @daysToAdd int

--Monday 
set @TransactionDate = '2011-05-02' 
set @transactionDay = datepart(dw,@TransactionDate)  

--Wednesday 
set @startDate = '2011-04-27' 
set @startDay = datepart(dw,@startDate)  

print @transactionDay
print @startDay

if(@startDay <= @transactionDay)
    set @daysToAdd = (@startDay + 7) - @transactionDay
else
   set @daysToAdd = @startDay - @transactionDay

set @NextTransactionDate = Dateadd(Day,@daysToAdd,@TransactionDate)
print @NextTransactionDate

三寸金莲 2024-11-16 03:18:37

我不确定我是否听懂了你所说的。我认为您是说下一个 @TransactionDate 应该是 @startDate 所在的一周中的下一次出现的日期。

如果是这种情况,您可以尝试:

declare @tDay = datepart(w, @transactionDate)

set @NextTransactionDate = DATEADD(w, @startDay-@tDay, @TransactionDate);

我不确定您对 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 :

declare @tDay = datepart(w, @transactionDate)

set @NextTransactionDate = DATEADD(w, @startDay-@tDay, @TransactionDate);

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

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文