This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server 2012 introduced a new TSQL EOMONTH for returning the last day of the month that contains the specified date with an optional offset.
In this post, I am sharing syntax and an example to find the first Sunday of the next month using EOMONTH.
The Database Administrator needs this kind of solution for scheduling the database maintenance script at every first Sunday of the next month, so I prepared below script using EOMONTH.
The Syntax of EOMONTH:
|
1 |
EOMONTH ( start_date [, month_to_add ] ) |
Script to find first Sunday of the Next Month:
First, create sample table with data:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 |
Using EOMONTH:
|
1 2 3 4 5 |
SELECT DATEADD(DAY,8-DATEPART(WEEKDAY,DATEADD(DAY,0,EOMONTH([SampleDate]))) ,EOMONTH([SampleDate])) AS FirstSunday_ofTheNextMonth FROM tbl_Test_EOMONTH GO |
