CODEWITHSUNDEEP
sql-server-2012
Sanjay
Sanjay

Reputation: 37

Is it possible to get recent month, year and previous month, year in one column in sqlserver

Is it possible to get recent month, year (i.e March 2017) and previous month, year (i.e February2017) in one column in sqlserver 2012.

|Month & Year|
---------------
| March2017  |
| February2017|

Upvotes: 1

Views: 34

Answers (3)

SqlZim
SqlZim

Reputation: 38023

Using format() in sql server 2012+

select val='PrevMonth',dt=format(dateadd(month,datediff(month,0, getdate())-1,0),'MMMMMyyyy')
union all 
select val='CurrMonth',dt=format(dateadd(month,datediff(month,0, getdate())  ,0),'MMMMMyyyy')
union all 
select val='NextMonth',dt=format(dateadd(month,datediff(month,0, getdate())+1,0),'MMMMMyyyy')

But format() can be slower, take a look here: format() is nice and all, but… - Aaron Bertand

rextester demo: http://rextester.com/IWCV22529

returns:

+-----------+--------------+
|    val    |      dt      |
+-----------+--------------+
| PrevMonth | February2017 |
| CurrMonth | March2017    |
| NextMonth | April2017    |
+-----------+--------------+

Upvotes: 1

S3S
S3S

Reputation: 25112

I think you are wanting it in one row... maybe like:

SELECT
   CONVERT(VARCHAR(16),DATENAME(month,GETDATE())) +
   CONVERT(VARCHAR(16),DATEPART(YEAR,GETDATE())) + 
   ' | ' +  
   CONVERT(VARCHAR(16),DATENAME(month,DATEADD(MONTH,-1,GETDATE()))) +
   CONVERT(VARCHAR(16),DATEPART(YEAR,DATEADD(MONTH,-1,GETDATE())))

Otherwise you can just do:

SELECT
CONVERT(VARCHAR(16),DATENAME(month,GETDATE())) + CONVERT(VARCHAR(16),DATEPART(YEAR,GETDATE()))
UNION ALL
SELECT
CONVERT(VARCHAR(16),DATENAME(month,DATEADD(MONTH,-1,GETDATE()))) + CONVERT(VARCHAR(16),DATEPART(YEAR,DATEADD(MONTH,-1,GETDATE())))

Just replace GETDATE() with your column name, if this is to be used on a table.

Upvotes: 1

Lamak
Lamak

Reputation: 70638

Yes, there are several ways to do this. An example is:

SELECT DATENAME(MONTH,DATEADD(MONTH,-1*number,GETDATE())) + 
       DATENAME(YEAR,DATEADD(MONTH,-1*number,GETDATE())) [Month & Year]
FROM master.dbo.spt_values
WHERE type = 'P'
AND number <= 1;

Here is a demo of it.

And the results are:

╔══════════════╗
║ Month & Year ║
╠══════════════╣
║ March2017    ║
║ February2017 ║
╚══════════════╝

Upvotes: 0

Related Questions