How to create function which return month April to march

Question

How to create function which return month April to march in given year. like : Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20 Feb-20 Mar-20

Amit Mohanty · Answer

Hey try this CREATE FUNCTION GETMONTHS(@ YEAR NVARCHAR(4)) RETURNS @ Output TABLE (FYMONTH VARCHAR (6), FYID INT ) AS BEGIN DECLARE @STARTDT DATE = @ YEAR + '-04-01' DECLARE @ENDDT DATE = CAST (( CAST (@ YEAR AS INT ) + 1) AS NVARCHAR(4)) + '-03-31' INSERT INTO @ Output (FYMONTH, FYID) SELECT CONVERT ( CHAR (3), DATENAME( MONTH , DATEADD( MONTH , MONTHID , -1 ))) + '-' + RIGHT (@ YEAR , 2) NAME ,MONTHID FROM ( SELECT MONTH (DATEADD( MONTH , X.NUMBER, @STARTDT)) AS MONTHID FROM MASTER.DBO.SPT_VALUES X WHERE X.TYPE = 'P' AND X.NUMBER MONTH , @STARTDT, @ENDDT) ) A RETURN END SELECT * FROM DBO.[GETMONTHS] ( '2019' )

Nitin Sontakke · Answer

@Sohil, Why am I getting an impression that part of your answer is copied from my post here: https://www.c-sharpcorner.com/Forums/fetch-records-after-235959 I obviously don't mind copying. We all do. But please at least takes some pains to modify it so much that nobody ever gets suspect about it. Furthermore, what is the context of word 'ArrivalDate' in that function?

Sonu Gupta · Answer

Hi Sunil, Use like this - CREATE FUNCTION - CREATE FUNCTION [dbo].[FN_FYMONTH] (@Year int) RETURNS @Output TABLE (FYMONTH VARCHAR(6)) AS BEGIN INSERT INTO @Output(FYMONTH) SELECT FYMONTH FROM ( SELECT 'APR-' + RIGHT(@Year,2) AS FYMONTH,1 AS OrderBy UNION SELECT 'MAY-' + RIGHT(@Year,2) AS FYMONTH,2 AS OrderBy UNION SELECT 'JUN-' + RIGHT(@Year,2) AS FYMONTH,3 AS OrderBy UNION SELECT 'JUL-' + RIGHT(@Year,2) AS FYMONTH,4 AS OrderBy UNION SELECT 'AUG-' + RIGHT(@Year,2) AS FYMONTH,5 AS OrderBy UNION SELECT 'SEP-' + RIGHT(@Year,2) AS FYMONTH,6 AS OrderBy UNION SELECT 'OCT-' + RIGHT(@Year,2) AS FYMONTH,7 AS OrderBy UNION SELECT 'NOV-' + RIGHT(@Year,2) AS FYMONTH,8 AS OrderBy UNION SELECT 'DEC-' + RIGHT(@Year,2) AS FYMONTH,9 AS OrderBy UNION SELECT 'JAN-' + CONVERT(VARCHAR(2),(RIGHT(@Year,2) + 1)) AS FYMONTH,10 AS OrderBy UNION SELECT 'FEB-' + CONVERT(VARCHAR(2),(RIGHT(@Year,2) + 1)) AS FYMONTH,11 AS OrderBy UNION SELECT 'MAR-' + CONVERT(VARCHAR(2),(RIGHT(@Year,2) + 1)) AS FYMONTH,12 AS OrderBy ) A ORDER BY OrderBy RETURN End USE THE FUNCTION SELECT * FROM DBO.[FN_FYMONTH] (2019)

Sohil Ahmed · Answer

@Nitin Sontakke sir No disrespect I have a similar procedure in my databases from last 2 years... also in my research on answers to a particular problem in SQL, I found that sone can follow my procedure but the core function will always remain same no matter how you write the SQL

Nitin Sontakke · Answer

Hello Sunil, Here is a script of the function you are looking for: if exists ( select 1 from sys.objects where [ name ] = 'GetFYMonths' and [type_desc] = 'SQL_TABLE_VALUED_FUNCTION' ) drop function [dbo].[GetFYMonths]; go create function [dbo].[GetFYMonths]() returns @returnValue table ( [DateFrom] date ,[DateTo] date ,[ Name ] varchar (20) ) as begin --select * from [dbo].[GetFYMonths]() declare @dateIndex date = ltrim(str( year (getdate()) - case when month (getdate()) then 1 else 0 end )) + '-04-01' declare @intIndex integer = 1, @dateFrom date , @dateTo date while @intIndex begin select @dateFrom = @dateIndex, @dateIndex = dateadd( month , 1, @dateFrom) select @dateTo = dateadd( day , -1, @dateIndex) insert into @returnValue select @dateFrom, @dateTo, datename( month , @dateFrom) + ' ' + ltrim(str( year (@dateFrom))) select @intIndex += 1; end return ; end go

Sohil Ahmed · Answer

Create a Strored Procedure called sp_getmonths CREATE PROCEDURE sp_getmonths @dateFrom datetime, @dateTo datetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @records table ( [ArrivalDate] datetime not null ) declare @dateIndex datetime = @dateFrom while @dateIndex begin select @dateIndex = dateadd(MONTH, 1, @dateIndex) insert into @records select @dateIndex end select r.ArrivalDate from @records r order by r.ArrivalDate asc END GO and EXEC sp_getmonths @dateFrom = '2019-04-15', @dateto = '2020-03-15'