SQL Select previous 12 months based on date

Question

0.00/5 (No votes)

See more:

I am trying to get a list of the previous 12 months with any date input with the format as varchar so (201404 would be April 2014) and would return a list of all months up to 201305.
At the moment I have the following but it brings thefollowing 12 months, I need the previous 12 months in format 201404.

SQL

DECLARE @Today DATETIME 
  , @nMonths TINYINT
SET @Today = GETDATE()
SET @nMonths = 12

SELECT   SUBSTRING(
			DATENAME(MONTH, DATEADD(dd, -1, DATEADD(month, n.n + DATEDIFF(month, 0, @Today), 0))), 1, 3) 
			+ CAST(YEAR(DATEADD(dd, -1, DATEADD(month , n.n + DATEDIFF(month, 0, @Today) -1 , 0))) AS VARCHAR(4))
		AS EndDateOP
FROM    ( SELECT TOP ( @nMonths )n = ROW_NUMBER() OVER ( ORDER BY NAME )
          FROM      master.dbo.syscolumns ) n
ORDER BY 1 DESC

Posted 2-Dec-15 22:39pm

Miss R

Updated 10-May-19 20:35pm

phil.o

v2

Add a Solution

Comments

F-ES Sitecore 3-Dec-15 4:59am

If you want to subtract months then add -12

Miss R 3-Dec-15 5:08am

Where and why would we do that?

jaket-cp 3-Dec-15 5:31am

you can fiddle with the @Today.
SET @Today = DATEADD(month, -12, GETDATE())

Miss R 3-Dec-15 5:35am

Thank you. Everything works fine but January is meant to be Jan2015 but it throws out Jan2014. How to fix this?

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-12-03T23:30:00

The problem is in

SQL

+ CAST(YEAR(DATEADD(dd, -1, DATEADD(month , n.n + DATEDIFF(month, 0, @Today) -1 , 0))) AS VARCHAR(4))

- see the -1 ... you're looking at the year of the previous month in the sequence - it should be

SQL

+ CAST(YEAR(DATEADD(dd, -1, DATEADD(month , n.n + DATEDIFF(month, 0, @Today), 0))) AS VARCHAR(4))

Here is another alternative for generating the same sequence - it will be slightly more performant as there is less formatting going on

SQL

DECLARE @Today DATETIME, @nMonths TINYINT
SET @nMonths = 12
SET @Today = DATEADD(month, (-1) * @nMonths, GETDATE())

;WITH q AS
(
	SELECT  @Today AS datum
	UNION ALL
	SELECT  DATEADD(month, 1, datum) 
	FROM q WHERE datum + 1 < GETDATE()
)
SELECT  SUBSTRING(DATENAME(MONTH, datum), 1, 3) + CAST(YEAR(datum) AS VARCHAR(4))
FROM q

taken from Manas' work at Generating a Sequence in SQL[^]

sandeepmittal11 · Answer 2 · 2015-12-03T23:45:00

Solution 2

SQL

DECLARE @Today DATETIME, @nMonths TINYINT
SET @nMonths = 12
SET @Today = GETDATE()
 
SELECT	RIGHT(CONVERT(VARCHAR,DATEADD(MM, -1*number, @Today),106),8)
FROM	master.dbo.spt_values
WHERE TYPE = 'P' and number between 1 and @nMonths

Posted 3-Dec-15 23:45pm

sandeepmittal11

Updated 3-Dec-15 23:50pm

v3

Chandrashekhar Amge · Answer 3 · 2019-05-10T20:35:00

DECLARE @Today DATETIME 
  , @nMONTHs TINYINT, @LinNum int = 100
SET @Today = DATEADD(MONTH, -12, GETDATE())
SET @nMONTHs = 12

SELECT
	n.n ID,
	DATENAME(MONTH, DATEADD(dd, -(DAY(GETDATE())+1), DATEADD(MONTH, n.n + DATEDIFF(MONTH, 0, @Today), 0))) + ' '
	+ CAST(YEAR(DATEADD(dd, -(DAY(GETDATE())+1), DATEADD(MONTH , n.n + DATEDIFF(MONTH, 0, @Today) , 0))) AS VARCHAR(4))	Name
FROM    ( SELECT TOP ( @nMONTHs )n = ROW_NUMBER() OVER ( ORDER BY NAME )
          FROM      master.dbo.syscolumns ) n
ORDER BY n.n

SQL Select previous 12 months based on date

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

SQL Select previous 12 months based on date

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us