I deal a lot in ISO dates (yyyymmdd) and need to generate the first and last day of the month very frequently. I spent a good deal of time trying to put these together and figured I would put it all together and share it. Here is what I have come up with.
I took some work from an existing article and added the CONVERT function to get the date format that I need.
--Get Current Date, Current Month Start, Current Month End, and Current Next Month start in ISO date format
SELECT
CONVERT (varchar(8),GETDATE(),112) Current_Day_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,0,GETDATE())-DAY(GETDATE())+1),112) CurrentMonth_start_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,1,GETDATE())-DAY(GETDATE())),112) CurrentMonth_End_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,1,GETDATE())-DAY(GETDATE()-1)),112) NextMonth_start_ISO
Here is another version that allows you to do the same from a declared date
--Get Declared Date, Declared Month Start, Declared Month End, and Declared Next Month start in ISO date format
DECLARE @dtDate DATE
SET @dtDate = '08/22/1998'
SELECT
CONVERT (varchar(8),@dtDate,112) Declared_Day_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,0,@dtDate)-DAY(@dtDate)+1),112) DeclaredMonth_start_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,1,@dtDate)-DAY(@dtDate)),112) DeclaredMonth_End_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,1,@dtDate)-DAY(@dtDate-1)),112) DeclaredMonthNextMonth_start_ISO
Credit goes to:
Pinal Dave in the main article and Param in the comments section of this blog post
http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/
Feel free to review my code and put any suggestions you might have in the comments!
Best Answer
From SQL Server 2012 you can use EOMONTH (Transact-SQL)