troubleshooting Question

MSSQL NEXT MONTH END DATE

Avatar of datatechcorp
datatechcorpFlag for United States of America asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL
10 Comments2 Solutions1006 ViewsLast Modified:
HELLO...

I have the following MSSQL statements:

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
DECLARE
  @NEXT_MONTH_BEGIN            DATETIME,
  @NEXT_MONTH_END            DATETIME

SET @NEXT_MONTH_BEGIN =  CONVERT(varchar(2), MONTH(dateadd(month,1,@mydate))) + '/1/' + CONVERT(varchar(4),YEAR(@mydate))
SET @NEXT_MONTH_END = DateAdd(month,1,@NEXT_MONTH_BEGIN) - 1
SELECT
@NEXT_MONTH_BEGIN as 'NEXT MONTH BEGIN',
@NEXT_MONTH_END as 'NEXT MONTH END'


These execute PERFECTLY for calculating my "NEXT MONTH END" date.  The only problem is...it does NOT work for '12-31-xxxx'...in that, for example, when 'mydate' is '12-31-2013', then the code will return '2013-01-31'.  In other words, it will not change over to the next year.  I have tested this with Leap Years, and all is good...except that one little glitch.  What am I doing wrong?  Please help!

Thanks in advance!...Mark
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros