Click here to Skip to main content
15,915,328 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
my input: I have 2 columns start date and end date based on these columns display the another column

Start date End date
8-Mar-14 12-May-14
15-Aug-14 20-Aug-14
12-May-14 15-Aug-14


Output: MOC column should display the Deference between start date and end date, values should be separated by comma

Start date End date Moc
8-Mar-14 12-May-14 march.april,may
15-Aug-14 20-Aug-14 august
12-May-14 15-Aug-14 may,june,july,august
Posted
Updated 30-Jun-14 21:12pm
v2

The Above solution i have modified here check this :
SQL
CREATE FUNCTION [dbo].FUN_GETMONTH(@date1 datetime, @date2 datetime)
RETURNS VARCHAR(1000)
AS
BEGIN

declare @tmp varchar(MAX)
 SET @tmp = ''
;with cte as (
select datename(month,@date1) as [Month_Name],@date1 as dat
union all 
select datename(month,DateAdd(Month,1,dat)),DateAdd(Month,1,dat) from cte 
where convert(char(6),DateAdd(Month,1,dat),112) <= convert(char(6),@date2,112)) 

select @tmp = @tmp +  CASE WHEN [Month_Name]='01' THEN 'JAN'
			 WHEN [Month_Name]='02' THEN 'FEB'
			  WHEN [Month_Name]='03' THEN 'MAR'
			   WHEN [Month_Name]='04' THEN 'APR'
			    WHEN [Month_Name]='05' THEN 'MAY'
			     WHEN [Month_Name]='06' THEN 'JUN'
			      WHEN [Month_Name]='07' THEN 'JUL'
			       WHEN [Month_Name]='08' THEN 'AUG'
			        WHEN [Month_Name]='09' THEN 'SEP'
			         WHEN [Month_Name]='10' THEN 'OCT'
			          WHEN [Month_Name]='11' THEN 'NOV'
			           WHEN [Month_Name]='12' THEN 'DEC' END + ', ' from CTE
			           option (maxrecursion 0)
			           RETURN @tmp;
 
END



SQL
DECLARE @TBL TABLE
(
    STRARTDATE DATETIME,
    ENDDATE    DATETIME
)
 
INSERT INTO @TBL(STRARTDATE, ENDDATE)
SELECT '2014-01-20', '2014-04-20'
UNION ALL
SELECT '2014-05-20', '2014-09-20'
UNION ALL
SELECT '2014-07-20', '2014-07-20'
UNION ALL
SELECT '2014-06-20', '2014-11-20'

select STRARTDATE,ENDDATE, dbo.FUN_GETMONTH(STRARTDATE, ENDDATE) AS MONTHS from @TBL
 
Share this answer
 
Comments
sasidharraju 1-Jul-14 5:30am    
if more the 10000 recodes how can I insert
Hi,

Try this...

SQL
DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;

SELECT   @StartDate = '8-Mar-2014'
        ,@EndDate   = '8-May-2014';


SELECT  DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'
AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);



Cheers
 
Share this answer
 
Below function will do the trick. First create the below function

/****** Object:  UserDefinedFunction [dbo].[FN_GET_DEFORM_MONTHS]    Script Date: 07/01/2014 12:14:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].FN_GET_DEFORM_MONTHS(@START_MONTH INT, @END_MONTH INT)
RETURNS VARCHAR(1000)
AS
BEGIN

	DECLARE @RetVal VARCHAR(1000)
	SELECT @RetVal = ''

	;WITH n(n) AS
	(
		SELECT 1
		UNION ALL
		SELECT n+1 FROM n WHERE n < 12
	)
	SELECT @RetVal=@RetVal + Coalesce(DATENAME(MONTH,DATEADD(MONTH,N-1,'01-Jan-1900'))+ ', ','') 
	FROM n 
	WHERE n BETWEEN @START_MONTH and @END_MONTH
	ORDER BY n 
	OPTION (MAXRECURSION 12);  
	
    RETURN LEFT(@RetVal, Len(@RetVal)-1);

END


Once function is created use below script to get desired result

SQL
DECLARE @TBL TABLE
(
    STRARTDATE DATETIME,
    ENDDATE    DATETIME
)

INSERT INTO @TBL(STRARTDATE, ENDDATE)
SELECT '08-Mar-2014', '12-May-2014'
UNION ALL
SELECT '15-Aug-2014', '20-Aug-2014'
UNION ALL
SELECT '12-May-2014', '15-Aug-2014'
UNION ALL
SELECT '01-Jan-2014', '01-Dec-2014'


select *, dbo.FN_GET_DEFORM_MONTHS(DATEPART(m, STRARTDATE), DATEPART(m, ENDDATE))
from @TBL
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900