my code stock.com

Function which returns a list of formatted month based on a start month and a number of month to generate.
Example :
select * from tvf_generateMonth('20120105', 5)
returns :
201201
201202
201203
201204
201205

Snippet options

Download: Download snippet as generates-months.sql.
Copy snippet: For this you need a free my code stock.com account.
Embed code : You will find the embed code for this snippet at the end of the page, if you want to embed it into a website or a blog!

/****** Object:  UserDefinedFunction [dbo].[tvf_generateMonth]    Script Date: 04/11/2012 08:37:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/* ***************************************************************************************************
 * Project Name:          	THOT
 * Project Version:      	$VersionInfo$
 * Creation Date:         	5 january 2012
 * Author:                	dfe
 * Classes Name:          	$ClassName$
 * General Description:	
Function which returns a list of formatted month based on a start month and a number of month to generate.
Example :
select * from tvf_generateMonth('20120105', 5)
returns :
	201201
	201202
	201203
	201204
	201205
 * References:            	$Reference$
 * ====================================================================================================
 * MODIFICATIONS
 * ----------------------------------------------------------------------------------------------------
 * Date            	Author        			Description			Project id
 * ----------------------------------------------------------------------------------------------------
 * Please complete after each code modification.
 * <date>          	<author>      			<fix id>			<documentation id>
 * <description of changes>
 * ************************************************************************************************* */
CREATE FUNCTION [dbo].[tvf_generateMonth]
(	
	@StartMonth as datetime,
	@Number as int
)
RETURNS TABLE 
AS
RETURN 
(
	with mycte as
	(
		select  @StartMonth as DateValue
		union all
		select DATEADD(mm, 1, DateValue)
		from    mycte   
		where   DATEADD(mm, 1, DateValue) < DATEADD(MM, @Number, @StartMonth)
	)

	SELECT 
		SUBSTRING(CONVERT(nvarchar, DateValue, 112), 1, 6) as [Month]
	FROM mycte
)

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Function which returns a list of formatted month based on a start month and a number of month to generate.
Example :
select * from tvf_generateMonth(''20120105'', 5)
returns :
	201201
	201202
	201203
	201204
	201205' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'tvf_generateMonth'
GO

Create a free my code stock.com account now.

my code stok.com is a free service, which allows you to save and manage code snippes of any kind and programming language. We provide many advantages for your daily work with code-snippets, also for your teamwork. Give it a try!

Find out more and register now

You can customize the height of iFrame-Codes as needed! You can find more infos in our API Reference for iframe Embeds.