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
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 
SET @dtDate = '08/22/1998'
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, 2016 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, 2016 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. Aug 18, 2016 at 1:08
  • @AdamHoge: Here's some info on how to ask & answer your own question: stackoverflow.com/help/self-answer
    – AMtwo
    Aug 18, 2016 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, 2016 at 4:57

1 Answer 1


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

       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.