SQL Server Portal

I came across this query when I was automating one of my services for a customer and it had to run on first Sunday of every month. I already had this solution earlier and utilized it in many places, but as my customer is using SQL Server 2012, I would like to write this script in a different way with less no of codes. So I started scripting and finally developed it using EOMONTH function. (A new function shipped with SQL Server 2012).

Let me create a sample to demonstrate the solution.

USE tempdb
GO
--DROP TABLE tbl_Sample
--GO
CREATE TABLE tbl_Sample
(
[ID] INT,
[Date] DATETIME
)
GO
INSERT INTO tbl_Sample VALUES (1,'2013-01-05')
INSERT INTO tbl_Sample VALUES (2,'2013-02-01')
INSERT INTO tbl_Sample VALUES (3,'2013-05-05')
INSERT INTO tbl_Sample VALUES (4,'2013-07-20')
INSERT INTO tbl_Sample VALUES (5,'2013-08-28')

GO

SOLUTION 1 : Using EOMONTH
Given below is the script.

USE tempdb
GO
SELECT DATEADD(DAY,8-
DATEPART(WEEKDAY,DATEADD(DAY,1,EOMONTH([Date])))
,EOMONTH([Date])) AS [First Sunday of the next month]
FROM tbl_Sample
GO
--OUTPUT

SOLUTION 2 : Using Traditional Method
Given below is the script.

USE tempdb
GO
SELECT CONVERT(DATE,DATEADD(DAY,8-
DATEPART(WEEKDAY,DATEADD(MONTH,DATEDIFF(MONTH, 0, [Date])+1,0))
,DATEADD(MONTH,DATEDIFF(MONTH, 0, [Date])+1, -1)))
AS [First Sunday of the next month]
FROM tbl_Sample
GO
--OUTPUT