Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get first Sunday of next month using 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?

Thanks

like image 500
Matt Avatar asked Oct 01 '09 19:10

Matt


People also ask

How do I get the next Sunday date in SQL Server?

Explanation: weekday(now()) returns the current weekday (starting with 0 for monday, 6 is sunday). Subtract the current weekday from 6 and get the remaining days until next sunday as a result. Then add them to the current date and get next sunday's date.

How do I get the number of Sundays in a month in SQL?

Here's one technique in brief: to identify a specific month, construct a date for the first day of that month. use the INTEGERS table to generate a series of dates beginning with the first day of that month, to cover all dates in the month. use a date function to determine if the generated date is a Sunday.


2 Answers

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:

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

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
like image 99
Charles Bretana Avatar answered Sep 22 '22 23:09

Charles Bretana


Just an FYI rather then coming up with some code to do this how about using a calendar 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 help: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=99696

like image 29
JonH Avatar answered Sep 25 '22 23:09

JonH