1

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!

  • Is there a question here? – dfundako Aug 17 '16 at 18:14
  • Nope. This is a case of I couldn't find it, so I built it, and posted it to share. How should I of done this? – Adam Hoge Aug 17 '16 at 18:34
  • Pose your solution first as a question, then answer it (once you've waited out the default time limit to answer your own question), and make it a Community Wiki. – Randolph West Aug 18 '16 at 1:08
  • @AdamHoge: Here's some info on how to ask & answer your own question: stackoverflow.com/help/self-answer – AMtwo Aug 18 '16 at 1:12
  • got it! I just need to get a better reputation, and I get the checkbox, or just chill a bit and answer it after 48 hours. Thanks for the link! – Adam Hoge Aug 18 '16 at 4:57
1

From SQL Server 2012 you can use EOMONTH (Transact-SQL)

SELECT DATEADD(DAY, 1-DAY(GETDATE()), GETDATE()) AS CurrentMonth_start,
       EOMONTH(GETDATE()) AS CurrentMonth_End,
       DATEADD(DAY, 1, EOMONTH(GETDATE())) AS NextMonth_start,
       EOMONTH(GETDATE(), 1) AS NextMonth_End;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.