T-SQL星期几查询

Question

T-SQL星期几查询

sql-servert-sqldate

5

我被要求完成一个T-SQL挑战，需要根据以下日期位置获取下个月的等效日期：

今天是2011年7月18日，换句话说，这个月是第三个星期一...

现在我需要在SQL Server查询中获取下个月的第三个星期一（2011年8月15日）。

这就像谷歌日历的重复规则。

我尝试了很多公式，但变得非常复杂，希望能得到任何帮助。

- Juan Jimenez

1

这不就是：1）找出今天是哪一天（本月的第三个星期一）2）找到下个月同类型的第一天（例如，下个月的第一个星期一）3）如果我们需要第二个或更多，就增加相应的7天？ - Lasse V. Karlsen

7个回答

2

CREATE FUNCTION fnSameDayOfWeekNextMonth (@Date datetime)
RETURNS datetime
AS BEGIN
  RETURN (
    SELECT
      DATEADD(WEEK,
              CASE (MONTH(ApproxDate) - MONTH(@Date) + 12) % 12
                WHEN 2 THEN -1
                ELSE ThisDayNum - (DAY(ApproxDate) - 1) / 7
              END,
              ApproxDate)
    FROM (
      SELECT
        ThisDayNum = (DAY(@Date) - 1) / 7,
        ApproxDate = DATEADD(WEEK, 5, @Date)
    ) s
  )
END

该函数实现以下逻辑：

将给定日期加上5周，得到一个大致日期。
获取给定日期所在月份中该星期的天数。
如果大致日期的月份比给定日期的月份晚2个月，则从大致日期中减去1周并返回结果。
否则，获取与#2相同的内容，但是针对大致日期（而不是给定日期）。
获取#2和#4之间的差异。
将#5作为周数从大致日期中减去并返回结果。

第3步意味着，如果当前日期是某个月的第五个星期几，则结果日期将是下个月的第四个同一星期几，因为第五个星期几是不可能的。更新：@t-clausen.dk提供了一个更好的想法，在这种情况下返回NULL。可以轻松修改上述函数以遵循相同的约定：只需将…WHEN 2 THEN -1…部分更改为…WHEN 2 THEN NULL…。

- Andriy M

你好，有什么办法可以跳过任意数量的月份吗？例如，从现在开始的4个月中的第三个星期一。 - Juan Jimenez

@Milox：是的，我有一个非常模糊的想法，目前正在尝试基于上述解决方案进行开发。到目前为止，问题与您最初的问题有很大不同，尽管两者非常相关。如果您真的有兴趣解决它，我建议您将请求发布为新问题。可能会有人比我更快地回答。 - Andriy M

谢谢@Andriy M，我已经发布了一个我开发的解决这种情况的函数，虽然不是很清晰，但它可以工作。请看下面。 - Juan Jimenez

1

幸运的是，我曾经做过这件事：http://blog.prokrams.com/2007/06/18/determining-the-ordinal-of-a-weekday/

Create Function fn_ReturnOrdinalDay( @TestDate datetime) returns int
begin

 declare@ordinal int,@loopdate datetime

set @loopdate = @TestDateset 
set @ordinal = 0

while datepart(m, @TestDate) = datepart(m, @loopdate)
  begin
            set @ordinal = @ordinal + 1
            set @testdate = dateadd(d, -7, @testdate)
  end

return @ordinal
end

预计完成时间：要找到与今天相同序数的下一天，只需将今天的日期加上+7，直到fn_ReturnOrdinalDay返回与今天相同的结果即可。

- mletterle

嗨，我做了一些数学运算，得出了这个结果：ceiling( cast(datepart(day,getdate())as float)/7 )这将从您的日期开始倒序计算，以获取序数分割并向上舍入。 - Juan Jimenez

1

我会使用一个日历表来完成这个任务：您可以为每天填充一行，然后只需要编写一个简单的查询来回答您的问题。您仍然可以使用其他帖子建议的函数和逻辑来填充表格，但那将是一次性任务。如果您经常处理重复日期，可以在列中使用字符串值“第一个星期一”，“第三个星期三”等，以便更轻松地查询。

您的查询可能类似于以下内容：

select 
    min(BaseDate)
from 
    dbo.Calendar
where 
    DayDescription = (select DayDescription from dbo.Calendar where BaseDate = @Today) and
    BaseDate > @Today

换句话说，给我今天之后第一个 DayDescription（例如“第三个星期一”或其他）与今天相同的日期。该日期必须是下个月的第三个星期一。确切的查询取决于您的数据类型和搜索参数值。

总的来说，许多（大多数？）与日期相关的查询使用预填充表比函数更容易回答。

- Pondlife

谢谢，我相信这样会更快，但对我来说行不通，因为我的日期可能从今天到十年甚至更久远。 - Juan Jimenez

@Milox - 为什么它不能工作呢？您可以预先填充尽可能多的数据。我有一个拥有100年数据的日历表，仅有36500行（加上闰年）。无论您填充10、100还是1000年，都没有关系，因为这是一项一次性任务，而且数据存储与数据库的其余部分相比微不足道。 - Pondlife

抱歉@Pondlife，我耽搁了一下，我一直在处理日期，这似乎是一个很好的方法，你说得对，它会有效。 - Juan Jimenez

0

这可能会对某些人有所帮助，如果您想要获取任意月数后的序数日期，我已经结合了所有建议创建了这个函数：

 CREATE FUNCTION [dbo].[fncSameDayOfWeekAnotherMonth] (@Date datetime, @Months int)
RETURNS datetime
AS BEGIN
  declare @FONM datetime,  @day TinyInt, @ordinal tinyint, @FDM datetime, @result datetime

  Set @FONM = DATEADD(dd,-(DAY(DATEADD(mm,@Months,@Date))-1),DATEADD(mm,@Months,@Date))--first day of month
  Set @day = datepart(weekday,@Date)+7
  set @ordinal=ceiling( cast(datepart(day,@Date)as float)/7 )--ordinal day
  set @FDM = DateAdd(day, ((@day -DatePart(weekday, @FONM))%7), @FONM)--first day of week (monday, friday, etc) on month

  set @result = DateAdd(day, (@ordinal-1)*7, @FDM)

    RETURN case when DATEDIFF(month, @Date, @result) >  @Months  then DateAdd(day, (@ordinal-2)*7, @FDM) else @result end --if result exceeds number of months just subtract one week from ordinal

END

它接收日期和月份数量，获取该天的序号和您想要的月份的第一天。通过最后一个来查找该月中星期几的第一次出现，然后计算从您的日期开始的任何月份的相应序数日期。

我知道它有很多代码，可能需要改进，但至少它运行得非常好。

- Juan Jimenez

在某些情况下，如果@Date是其月份中某个工作日的第5次出现，并且计算出的月份中对应的日期未定义，则您的函数返回同一工作日的第4次出现，而不是NULL。但我理解这是您对这种情况的特殊偏好。除此之外，这个函数似乎工作良好，只是在年度过渡时会失败。我认为可以通过使用DATEDIFF(mm, …) > @Month而不是MONTH(…) > MONTH(…) + @Month来修复这个问题。 - Andriy M

哪个日期不起作用了？我已经测试了几种情况，它可以一直工作到明年。 - Juan Jimenez

SELECT dbo.fncSameDayOfWeekAnotherMonth('20111129', 3)。我应该说，在年份转换的某些情况下会失败。这些“某些情况”是指当日期是工作日＃5，并且您指定的月份将比原始月份少，并且没有那个工作日＃5时。这就是为什么DATEDIFF会更可靠的原因。 - Andriy M

你说得对，即使使用datediff我也得到了一个错误的日期，我得想办法解决。 - Juan Jimenez

我把datediff的参数搞反了，现在已经修正和编辑过了，谢谢@Andriy M - Juan Jimenez

当然，您决定通过接受自己的答案来为社区做出贡献是值得称赞的。您的解决方案肯定比其他方案更好。但这只是因为它解决了比您的问题所要求的更普遍的问题。您可能从社区支持中失去了受益的机会。如果您像我建议的那样将更普遍的问题发布为新问题，您可能会收到更好的替代方案。 - Andriy M

0

如果你只需要完成上面的挑战，那么这相对来说比较容易。

使用日期格式函数（我记不清确切名称）找出当天的名称。
从该月份开始循环，并在到达输入日期之前计算出你遇到的例如星期二等工作日的数量。
然后循环下一个月（或任何月份）x次，在达到所需数量后，取下一个星期二。

- Arj

0

-- 对于每月的第三个星期三

DATEPART(dw, PromptDate) + DATEPART(dw, PromptDate - 7) + DATEPART(dw, PromptDate - 14) = 12

-- 我们只关心每月的第三个星期三，不应该是第四或第五个星期三。

AND DATEDIFF(MONTH, PromptDate - 14, PromptDate - 21) <> 0

- Edmun Lau

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- t-clausen.dk · Accepted Answer

这个语法将计算下一周的同一天，并在不存在时放入 null。

declare @t datetime

set @t = '2008-01-29'

select case (datepart(day, @t)+ 6) / 7
           when (datepart(day, @t + 28)+ 6) / 7 then @t + 28
           when (datepart(day, @t + 35)+ 6) / 7 then @t + 35
           else null end