How to find the first and last days in years, months etc
From Wiki
This example will return the first and last day for the year, quarter, month and week for any date passed in.
The example date is October 10th 2007, change that to any date you want
- DECLARE @d DATETIME
- SET @d = '20071010'
- SELECT
- DATEADD(yy, DATEDIFF(yy, 0, @d), 0) AS FirstDayOfYear,
- DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1) AS LastDayOfYear,
- DATEADD(qq, DATEDIFF(qq, 0, @d), 0) AS FirstDayOfQuarter,
- DATEADD(qq, DATEDIFF(qq, 0, @d)+1, -1) AS LastDayOfQuarter,
- DATEADD(mm, DATEDIFF(mm, 0, @d), 0) AS FirstDayOfMonth,
- DATEADD(mm, DATEDIFF(mm, 0, @d)+1, -1) AS LastDayOfMonth,
- @d - DATEDIFF(dd, @@DATEFIRST - 1, @d) % 7 AS FirstDayOfWeek,
- @d - DATEDIFF(dd, @@DATEFIRST - 1, @d) % 7 + 6 AS LastDayOfWeek
Contributed By: --SQLDenis 16:41, 30 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section Dates
