使用T-SQL获取下个月的第一个星期日

Looking for a way to get the date in the format "11/1/2009", which would be the first sunday of next month. I want to run this query after the first sunday in october to get the first sunday of the upcoming month. What is the best method to accomplish this with a T-SQL query?

正在寻找一种方式来获取“11/1/2009”格式的日期，这将是下个月的第一个星期日。我想在10月的第一个星期日之后运行这个查询，以获得即将到来的月份的第一个星期日。使用T-SQL查询实现此目的的最佳方法是什么？

Thanks

谢谢

9 个解决方案

#1

try this:

尝试这个：

Declare @D Datetime 
Set @D = [Some date for which you want the following months' first sunday]
Select DateAdd(day, (8-DatePart(weekday, 
    DateAdd(Month, 1+DateDiff(Month, 0, @D), 0)))%7, 
    DateAdd(Month, 1+DateDiff(Month, 0, @D), 0))

EDIT Notes:

编辑备注：

The first of next Month is given by the expression:

下个月的第一个由表达式给出：

DateAdd(Month, 1+DateDiff(Month, 0, @D), 0)

or by: which can be modified to give the first of the month two months from now by changing the 1 to a 2:

或者：通过将1更改为2，可以修改为从现在开始两个月的第一个月：

DateAdd(Month, 2+DateDiff(Month, 0, @D), 0)

EDIT: In response to @NissanFan, and @Anthony: to modify this to return the first Monday Tuesday Wednesday, etc, change the value 8 to a 9, 10, 11, etc....

编辑：回应@NissanFan和@Anthony：要修改它以返回周三的第一个星期一，等等，将值8更改为9,10,11等....

Declare @Sun TinyInt Set @Sun = 8
Declare @Mon TinyInt Set @Mon = 9
Declare @Tue TinyInt Set @Tue = 10
Declare @Wed TinyInt Set @Wed = 11
Declare @Thu TinyInt Set @Thu = 12
Declare @Fri TinyInt Set @Fri = 13
Declare @Sat TinyInt Set @Sat = 14
Declare @D Datetime, @FONM DateTime -- FirstofNextMonth 
Set @D = [Some date for which you want the following months' first sunday]
Set @FONM = DateAdd(Month, 1+DateDiff(Month, 0, @D),0)

Select 
  DateAdd(day, (@Sun -DatePart(weekday, @FONM))%7, @FONM) firstSunInNextMonth,
  DateAdd(day, (@Mon -DatePart(weekday, @FONM))%7, @FONM) firstMonInNextMonth,
  DateAdd(day, (@Tue -DatePart(weekday, @FONM))%7, @FONM) firstTueInNextMonth,
  DateAdd(day, (@Wed -DatePart(weekday, @FONM))%7, @FONM) firstWedInNextMonth,
  DateAdd(day, (@Thu -DatePart(weekday, @FONM))%7, @FONM) firstThuInNextMonth,
  DateAdd(day, (@Fri -DatePart(weekday, @FONM))%7, @FONM) firstFriInNextMonth,
  DateAdd(day, (@Sat -DatePart(weekday, @FONM))%7, @FONM) firstSatInNextMonth

#2

Just an FYI rather then coming up with some code to do this how about using a calendar table.

只是一个FYI而不是提出一些代码来做这个如何使用日历表。

Take a look at this: http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

看看这个：http：//sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

This also may help: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=99696

这也可能有所帮助：http：//www.sqlteam.com/forums/topic.asp？TOPIC_ID = 99696

#3

Here is a query to get first working day of next month

这是查询下个月的第一个工作日

DECLARE @DAYOFWEEK INT,@ReminderDate DateTime
SET @DAYOFWEEK = DATEPART( WEEKDAY,DateAdd(D,- Day(GetDate())+1, DATEADD(M,1,GetDate())) )
Print @DAYOFWEEK
If @DAYOFWEEK = 1
Set @ReminderDate = DateAdd(D,- Day(GetDate())+2, DATEADD(M,1,GetDate()))
Else If @DAYOFWEEK =7
Set @ReminderDate = DateAdd(D,- Day(GetDate())+3, DATEADD(M,1,GetDate()))
Else
Set @ReminderDate = DateAdd(D,- Day(GetDate())+1, DATEADD(M,1,GetDate()))
Print @ReminderDate

#4

You can use DATENAME to determine the day you want, I might recommend a loop to move the date from the 01 of the month in question to get to the first sunday.

您可以使用DATENAME来确定您想要的日期，我可能会建议使用循环将日期从相关月份的01日移至第一个星期日。

So lets try:

所以试试吧：

DECLARE @DateTime DATETIME

Set to the date to start off with, then add 1 day until you find what you are looking for. Use datename with dw...

设置为开始日期，然后添加1天，直到找到您要找的内容。使用dw的datename ...

We have used this to determine weekends, but holidays will be a problem, where we use a table to store that.

我们已经用它来确定周末，但假期将是一个问题，我们使用表来存储它。

#5

Try this code as a function:

尝试将此代码作为函数：

-- Variables
DECLARE @DATE DATETIME
DECLARE @DAY INT
DECLARE @DAYOFWEEK INT
DECLARE @TESTDATE DATETIME

-- Set
SET @TESTDATE = GETDATE()
SET @DATE = DATEADD( MONTH, 1, @TESTDATE )
SET @DAY = DATEPART( DAY, @TESTDATE )
SET @DATE = DATEADD( DAY, -@DAY + 1, @DATE )
SET @DAYOFWEEK = DATEPART( WEEKDAY, @DATE )
IF @DAYOFWEEK > 1
BEGIN
    SET @DAYOFWEEK = 8 - @DAYOFWEEK
END
ELSE
BEGIN
    SET @DAYOFWEEK = 0
END

SET @DATE = DATEADD( DAY, @DAYOFWEEK, @DATE )

-- Display
PRINT @TESTDATE
PRINT @DAY
PRINT @DAYOFWEEK
PRINT @DATE

#6

Here is the non-system specific way to determine the first Sunday of the following month:

以下是确定下个月第一个星期日的非系统特定方法：

First, get the current month and add one month. Next, set the date of that variable to be on the first. Next, find the day value of that date (let's assume Mondays are 1 and Sundays are 7). Next, subtract the day value of the 1st of the month from the day value of Sunday (7).

首先，获取当前月份并添加一个月。接下来，将该变量的日期设置为第一个。接下来，找到该日期的日期值（假设星期一是1，星期日是7）。接下来，从星期日（7）的日值中减去1月1日的日值。

You now have the number of days between the first of the month and the first Sunday. You could then add that to the date variable to get the first Sunday, or, since we know the first of the month is 1, you could just add one to the difference (found in that last step above) and that is the date of the first Sunday. (You have to add one because it's subtracting and thus if the first of the given month IS Sunday, you'd end up with 0).

您现在拥有该月的第一个星期日和第一个星期日之间的天数。然后，您可以将其添加到日期变量以获取第一个星期日，或者，因为我们知道该月的第一个星期是1，您可以只添加一个差异（在上一步中找到），这是日期第一个星期天。（你必须添加一个，因为它正在减去，因此如果给定月份的第一个是星期日，你最终会得到0）。

I have been looking through the T-SQL documentation and it is not at all intuitive as to how how you would use my method, but you will need the concept of "day of week number" to make it work no matter what.

我一直在查看T-SQL文档，但是如何使用我的方法并不是很直观，但是你需要“星期数”的概念才能使它工作无论如何。

#7

This would be simplest with an auxiliary calendar table. See this link, for example.

这对于辅助日历表来说是最简单的。例如，请参阅此链接。

However, it can be done without one, though it's rather tricky. Here I assume you want the first future date that is the first Sunday of a month. I've written this with a variable @now for - well - now, so it's easier to test. It might be possible to write more simply, but I think this has a better chance of being correct than quickly-written simpler solutions. [Corrected by adding DAY(d) < 8]

但是，它可以在没有一个的情况下完成，尽管它相当棘手。在这里，我假设您希望第一个未来日期是一个月的第一个星期日。我已经用变量@now写了这个 - 好 - 现在，所以它更容易测试。可能更简单地编写，但我认为这比快速编写更简单的解决方案更有可能是正确的。 [更正日期（日期）<8]

SET @now = GETDATE();
WITH Seven(i) AS (
  SELECT -1 UNION ALL SELECT 0 UNION ALL SELECT 1
            UNION ALL SELECT 3 UNION ALL SELECT 4
            UNION ALL SELECT 5 UNION ALL SELECT 6
), Candidates(d) AS (
  SELECT DATEADD(WEEK,i+DATEDIFF(WEEK,'19000107',@now),'19000107')
  FROM Seven
) 
  SELECT TOP (1) d AS SoonestFutureFirstSunday
  FROM Candidates
  WHERE DAY(d) < 8 AND MONTH(d) >= MONTH(@now)
  AND (MONTH(d) > MONTH(@now) OR DAY(d) > DAY(@now)) 
  ORDER BY d;      ORDER BY d;

#8

I reckon that the answer is this

我估计答案是这样的

SELECT  DATEADD(Month, DATEDIFF(Month, 0, GETDATE()) + 1, 0) + 6 - (DATEPART(Weekday,
                  DATEADD(Month,
                      DATEDIFF(Month,0, GETDATE()) + 1, 0))
                  + @@DateFirst + 5) % 7 --FIRST sunday of following month

#9

Reference taken from this blog:

从这篇博客中摘录：

SQL Server 2012 introduced one new TSQL EOMONTH to return the last day of the month that contains the specified date with an optional offset.

SQL Server 2012引入了一个新的TSQL EOMONTH，以返回包含指定日期和可选偏移量的月份的最后一天。

CREATE TABLE tbl_Test_EOMONTH
(
    SampleDate DATETIME
)
GO

INSERT INTO tbl_Test_EOMONTH VALUES ('2015-12-20')
INSERT INTO tbl_Test_EOMONTH VALUES ('2015-11-08')
INSERT INTO tbl_Test_EOMONTH VALUES ('2015-10-16')
INSERT INTO tbl_Test_EOMONTH VALUES ('2015-09-26')
INSERT INTO tbl_Test_EOMONTH VALUES ('2016-01-31') 
GO

SELECT 
    DATEADD(DAY,8-DATEPART(WEEKDAY,DATEADD(DAY,0,EOMONTH([SampleDate])))
    ,EOMONTH([SampleDate])) AS FirstSunday_ofTheNextMonth
FROM tbl_Test_EOMONTH
GO

#1