O R G A N I C / F E R T I L I Z E R: getting the first and last day of the month in sql

Jul 6, 2011

getting the first and last day of the month in sql

astronomical clock courtesy of simpologisti was putting this together for a friend of mine so i thought i would post it since it seems like a pretty useful thing to have. should be self-explanatory.

-- first and last for previous month
DECLARE @FirstDayPrev DATETIME
DECLARE @LastDayPrev DATETIME

-- first and last for current month
DECLARE @FirstDayCurr DATETIME
DECLARE @LastDayCurr DATETIME

Set @FirstDayPrev = CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0),101)
Set @LastDayPrev = CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(mm, 0,getdate())+0, -1),101)

Set @FirstDayCurr = CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(mm, 0,getdate())+0, 0),101)
Set @LastDayCurr = CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(mm, 0,getdate())+1, -1),101)

Select @FirstDayPrev, @LastDayPrev, @FirstDayCurr, @LastDayCurr