CODEWITHSUNDEEP
sql-server
user3909108
user3909108

Reputation: 3

how to get in between year and month where From year and month To month year using sql

MonthFROM  |  YearFROM  |  MonthTo | YearTO
---------------------------------------------
   02          2012          05       2012

Output Should be:

Month  Year
 02    2012
 03    2012
 04    2012
 05    2012

Upvotes: 0

Views: 66

Answers (2)

bummi
bummi

Reputation: 27367

You might use a CTE and convert your ranges to a datetime for easy handling.

declare @tab TABLE (ID int,MonthFROM int,  YearFROM  int,  MonthTo int, YearTO int)
insert into @tab Select 1,2,2012,5,2012


;With CTE(Start,aEND) as
(
Select CAST(
           CAST(YearFROM AS VARCHAR(4)) +
           RIGHT('0' + CAST(MonthFROM AS VARCHAR(2)), 2) + '01'   
           AS DATETIME) as Start
      ,CAST(
           CAST(YearTo AS VARCHAR(4)) +
           RIGHT('0' + CAST(MonthTo AS VARCHAR(2)), 2) + '01'   
           AS DATETIME) as aEND
from  @tab   
Where ID=1

UNION ALL
Select DATEADD(MM,1,Start),aEND from CTE  Where DATEADD(MM,1,Start)<=aEND                   

)
Select DATEPART(MM,Start) as [Month],DATEPART(YY,Start) as [Year] from CTE

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44316

Using master..spt_values as tally table (maximum 2047 months otherwise use another tally table):

DECLARE
  @MonthFROM int = 2,
  @YearFROM  int = 2012, 
  @MonthTo   int = 5,
  @YearTO    int = 2012

DECLARE
  @from datetime = dateadd(month, @MonthFROM-1, cast(@YearFrom as char(4)))
DECLARE
  @to datetime = dateadd(month, @MonthTO-1, cast(@YearTo as char(4)))

SELECT month(dateadd(m, number, @from)) Month, year(dateadd(m, number, @from)) Year
FROM
   master..spt_values
WHERE 
  type = 'P' and
  number <= datediff(month, @from, @to)

Result:

Month Year
2     2012
3     2012
4     2012
5     2012

Upvotes: 1

Related Questions