How to extract just the month from next month using TSQL?

In TSQL, I need to extract just the month as 2 characters (01,02,03, etc) from NEXT month.  Is there an easy way to do that please?  Should have been clearer, that I am not extracting from any particular table, just want the ability to capture next month as 01, 02, 03.  So if today is Jan 01, 2011 when I run, I would want to extract a field  '02' for next month
LVL 1
ibgadminAsked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:

select * from customerdemo
where left(demographics, 2) = CONVERT(VARCHAR(2), DATEADD(MM, 1, GETDATE()), 101)
0
 
HainKurtSr. System AnalystCommented:
here it is:

right('0' +
cast(
(
case datepart(month,getdate())
when 12 then 1
else datepart(month,getdate())+1 end
)
as varchar(2)
),2)
0
 
HainKurtSr. System AnalystCommented:
or simpler form using modulus

right('0' +
cast(
(datepart(month,getdate()) % 12)+1
as varchar(2)
),2)
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Philippe DamervalSenior Analyst ProgrammerCommented:
Or you could try this, perhaps a little simpler:

RIGHT('00' + LTRIM(STR(MONTH(DATEADD(m, 1, getDate())))), 2)

0
 
lavan_8Commented:
SELECT     SUBSTRING(CONVERT(char(5), DATEADD(m, 1, GETDATE()), 3), 4, 2) AS Expr1
0
 
HainKurtSr. System AnalystCommented:
I guess nobody think of

Month 12 --> 13 ????
0
 
HainKurtSr. System AnalystCommented:
sorry, you used dateadd functions :) my bad...
0
 
Ephraim WangoyaCommented:

SELECT DATEPART(MM, DATEADD(MM, 1, GETDATE()))
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Ewangoya, your expression does not return the leading zero required by the question.
0
 
Ephraim WangoyaCommented:

SELECT CONVERT(VARCHAR(2), DATEADD(MM, 1, GETDATE()), 101)
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Yes, this works, but only in the US. the expressions using MONTH or DATEPART will work internationally.
0
 
Ephraim WangoyaCommented:


@damerval
What do you mean works only in the US?, you already specify the format you want "101", that will return a consistent format anywhere in the world
0
 
ibgadminAuthor Commented:
All of the above worked, but guess I needed more to get it to work for my script.  I have a specific table field that begins with the next month.  It's a long string data field and the month is in the first two chars.....How would I do this with a table called 'customerdemo' and fieldname called 'demographics' is equal to the next month (01, 02, 03...)  So sorry I wasn't clearer to get to my end result.
0
 
Ephraim WangoyaCommented:

I'm not sure I understood your table structure but my post can give you basis for the solution

Use LEFT(yourfield, 2) to get the two left characters
0
 
ibgadminAuthor Commented:
That did it - thank you so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.