[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

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
0
ibgadmin
Asked:
ibgadmin
  • 5
  • 4
  • 3
  • +2
1 Solution
 
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
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Or you could try this, perhaps a little simpler:

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

0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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:

select * from customerdemo
where left(demographics, 2) = CONVERT(VARCHAR(2), DATEADD(MM, 1, GETDATE()), 101)
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now