[ACCEPTED]-Get first Sunday of next month using T-SQL-tsql

Accepted answer
Score: 10

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 7 is given by the expression:

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

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

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

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

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
Score: 1

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

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

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

Score: 1

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

So lets try:

DECLARE @DateTime DATETIME

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

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

Score: 1

Here is a query to get first working day 1 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
Score: 1

Reference taken from this blog:

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

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
Score: 0

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

0

Score: 0

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

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

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

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

Score: 0

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

However, it 9 can be done without one, though it's rather 8 tricky. Here I assume you want the first 7 future date that is the first Sunday of 6 a month. I've written this with a variable 5 @now for - well - now, so it's easier to 4 test. It might be possible to write more 3 simply, but I think this has a better chance 2 of being correct than quickly-written simpler 1 solutions. [Corrected by adding DAY(d) < 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;
Score: 0

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

0

More Related questions