T-sql select all months for several years

Question

I need to choose to return the month and year. Within a certain date range, in which I must enter the starting year and month, and select will return the month and year from the date I entered until today.

I know I can do this in a loop, but I was wondering if this can be done in a series?

Year Month ---- ----- 2010 1 2010 2 2010 3 2010 4 2010 5 2010 6 2010 7 

etc.

Answer 1

 declare @date1 datetime, @date2 datetime, @date datetime, @month integer, @nm_bulan varchar(20) create table #month_tmp ( bulan integer null, keterangan varchar(20) null ) select @date1 = '2000-01-01', @date2 = '2000-12-31' select @month = month(@date1) while (@month < 13) Begin IF @month = 1 Begin SELECT @date = CAST( CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,0,@date1))-1),DATEADD(mm,0,@date1)),111) + ' 00:00:00' as DATETIME ) End ELSE Begin SELECT @date = CAST( CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,@month -1,@date1))-1),DATEADD(mm,@month -1,@date1)),111) + ' 00:00:00' as DATETIME ) End select @nm_bulan = DATENAME(MM, @date) insert into #month_tmp select @month as nilai, @nm_bulan as nama select @month = @month + 1 End select * from #month_tmp drop table #month_tmp go 

Answer 2

Gosh people ... using "recursive CTE count" or "rCTE" is just as bad or worse than using a loop. Please refer to the following article why I say this.

http://www.sqlservercentral.com/articles/T-SQL/74118/

Here is one way to do this without RBAR, including the “hidden RBAR” of the rCTE counter.

 --===== Declare and preset some obviously named variables DECLARE @StartDate DATETIME, @EndDate DATETIME ; SELECT @StartDate = '2010-01-14', --We'll get the month for both of these @EndDate = '2020-12-05' --dates and everything in between ; WITH cteDates AS (--==== Creates a "Tally Table" structure for months to add to start date -- calulated by the difference in months between the start and end date. -- Then adds those numbers to the start of the month of the start date. SELECT TOP (DATEDIFF(mm,@StartDate,@EndDate) + 1) MonthDate = DATEADD(mm,DATEDIFF(mm,0,@StartDate) + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1),0) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2 ) --===== Slice each "whole month" date into the desired display values. SELECT [Year] = YEAR(MonthDate), [Month] = MONTH(MonthDate) FROM cteDates ; 

Answer 3

You can use something like this: http://blogs.msdn.com/b/sqlazure/archive/2010/09/16/10063301.aspx

To create an equivalent table of numbers using date ranges.

But could you clarify your ins and outs?

Do you want to enter a start date, for example, '2010-5-1' and an end date, for example, '2010-8-1' , and return it every month between them? Do you want to include the start month and end of the month or exclude them?

Here, some code that I wrote will quickly generate an inclusive result for each month between two dates.

 --Inputs here: DECLARE @StartDate datetime; DECLARE @EndDate datetime; SET @StartDate = '2010-1-5 5:00PM'; SET @EndDate = GETDATE(); --Procedure here: WITH RecursiveRowGenerator (Row#, Iteration) AS ( SELECT 1, 1 UNION ALL SELECT Row# + Iteration, Iteration * 2 FROM RecursiveRowGenerator WHERE Iteration * 2 < CEILING(SQRT(DATEDIFF(MONTH, @StartDate, @EndDate)+1)) UNION ALL SELECT Row# + (Iteration * 2), Iteration * 2 FROM RecursiveRowGenerator WHERE Iteration * 2 < CEILING(SQRT(DATEDIFF(MONTH, @StartDate, @EndDate)+1)) ) , SqrtNRows AS ( SELECT * FROM RecursiveRowGenerator UNION ALL SELECT 0, 0 ) SELECT TOP(DATEDIFF(MONTH, @StartDate, @EndDate)+1) DATEADD(month, DATEDIFF(month, 0, @StartDate) + A.Row# * POWER(2,CEILING(LOG(SQRT(DATEDIFF(MONTH, @StartDate, @EndDate)+1))/LOG(2))) + B.Row#, 0) Row# FROM SqrtNRows A, SqrtNRows B ORDER BY A.Row#, B.Row#; 

Answer 4

The code below generates values ​​for the range between July 21, 2013 and January 15, 2014 . I usually use it in SSRS reports to generate search values ​​for the Month parameter.

 declare @from date = '20130721', @to date = '20140115'; with m as ( select * from (values ('Jan', '01'), ('Feb', '02'),('Mar', '03'),('Apr', '04'),('May', '05'),('Jun', '06'),('Jul', '07'),('Aug', '08'),('Sep', '09'),('Oct', '10'),('Nov', '11'),('Dec', '12')) as t(v, c)), y as (select cast(YEAR(getdate()) as nvarchar(4)) [v] union all select cast(YEAR(getdate())-1 as nvarchar(4))) select mv + ' ' + yv [value_field], yv + mc [label_field] from m cross join y where yv + mc between left(convert(nvarchar, @from, 112),6) and left(convert(nvarchar, @to, 112),6) order by yv + mc desc 

Results:

 value_field label_field --------------------------- Jan 2014 201401 Dec 2013 201312 Nov 2013 201311 Oct 2013 201310 Sep 2013 201309 Aug 2013 201308 Jul 2013 201307 

Answer 5

--- Here is the version that gets the month end dates commonly used for accounting purposes

 DECLARE @StartDate datetime; DECLARE @EndDate datetime; SET @StartDate = '2010-1-1'; SET @EndDate = '2020-12-31'; --Procedure here: WITH RecursiveRowGenerator (Row#, Iteration) AS ( SELECT 1, 1 UNION ALL SELECT Row# + Iteration, Iteration * 2 FROM RecursiveRowGenerator WHERE Iteration * 2 < CEILING(SQRT(DATEDIFF(MONTH, @StartDate, @EndDate)+1)) UNION ALL SELECT Row# + (Iteration * 2), Iteration * 2 FROM RecursiveRowGenerator WHERE Iteration * 2 < CEILING(SQRT(DATEDIFF(MONTH, @StartDate, @EndDate)+1)) ) , SqrtNRows AS ( SELECT * FROM RecursiveRowGenerator UNION ALL SELECT 0, 0 ) SELECT TOP(DATEDIFF(MONTH, @StartDate, @EndDate)+1) DateAdd(d,-1,DateAdd(m,1, DATEADD(month, DATEDIFF(month, 0, @StartDate) + A.Row# * POWER(2,CEILING(LOG(SQRT(DATEDIFF(MONTH, @StartDate, @EndDate)+1))/LOG(2))) + B.Row#, 0) )) Row# FROM SqrtNRows A, SqrtNRows B ORDER BY A.Row#, B.Row#; 

Answer 6

 DECLARE @Date1 DATE DECLARE @Date2 DATE SET @Date1 = '20130401' SET @Date2 = DATEADD(MONTH, 83, @Date1) SELECT DATENAME(MONTH, @Date1) "Month", MONTH(@Date1) "Month Number", YEAR(@Date1) "Year" INTO #Month WHILE (@Date1 < @Date2) BEGIN SET @Date1 = DATEADD(MONTH, 1, @Date1) INSERT INTO #Month SELECT DATENAME(MONTH, @Date1) "Month", MONTH(@Date1) "Month Number", YEAR(@Date1) "Year" END SELECT * FROM #Month ORDER BY [Year], [Month Number] DROP TABLE #Month 

Answer 7

you can do the following

 SELECT DISTINCT YEAR(myDate) as [Year], MONTH(myDate) as [Month] FROM myTable WHERE <<appropriate criteria>> ORDER BY [Year], [Month] 

Answer 8

I know this is an old question, but I'm slightly scared by the complexity of some of the answers. Using CTE is definitely the easiest way to select these values:

 WITH months(dt) AS (SELECT getdate() dt UNION ALL SELECT dateadd(month, -1, dt) FROM months) SELECT top (datediff(month, '2017-07-01' /* start date */, getdate()) + 1) YEAR(months.dt) yr, MONTH(months.dt) mnth FROM months; 

Just type any start date instead of '2017-07-01' above, and you can use an efficient and easily integrated solution.

---

Change: Jeff Moden's answer quite effectively opposes the use of rCTE. However, in this case it seems to be a case of premature optimization - we are talking about 10 records in all likelihood, and even if you return to 1900 from today, this is still a minor blow. Using rCTE to enable code maintenance seems to pay off if the expected result set is small.