Concatenate Months between two dates

Question

I want to get months between two dates with Name and Concatenate them as single Field. Lets suppose i have Start Date = 2018-02-01 and End Date = 2018-04-01, Now i want to get the months between the dates and in concatinted string Like i want to get it as Feb-18,Mar-18,Apr-18 for the above mentioned dates as a single Field. Thanks

Suraj Kumar · Answer

Hello Javed, You can use below query for your output as you want in sql server. Here CTE table has been used. declare @start DATE = '2018-02-01' declare @ end DATE = '2018-04-01' DECLARE @test NVARCHAR( max ) ; with months ( date ) AS ( SELECT @start UNION ALL SELECT DATEADD( month ,1, date ) from months where DATEADD( month ,1, date ) end )+1,0)) ) SELECT @test = COALESCE (@test + ',' , '' ) + CAST (DATENAME( month , date ) AS CHAR (3)) + ' ' + RIGHT ( CONVERT ( VARCHAR (8), date, 1),2) FROM months SELECT field2 = @test Here to get the month name in three letters it has been used below sql statement CAST (DATENAME( MONTH , date) AS CHAR (3)) To get the last two numbers of year below it has been used below sql statement RIGHT ( CONVERT ( VARCHAR (8), date , 1),2) After extracting both values to concatename with comma COALESCE function of sql server has been used. Hope this will help you.

Rameez Javed · Answer

Suraj Kumar Thanks Buddy, Its works.

Udit Chauhan · Answer

Hi Rameez please go through below code : using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Globalization; namespace ConsoleApplication7 { class Program { static void Main( string [] args) { List string > lst = new List string >(); lst.Add( "2018-02-01" ); //feb-2018,march-2018,april-2018 lst.Add( "2018-04-01" ); string result = "" ; DateTime c; for ( int i=0; i { c = Convert.ToDateTime(lst[i]); if (i == (lst.Count-1)) { int difference = Convert.ToDateTime(lst[i]).Month - Convert.ToDateTime(lst[i - 1]).Month; for ( int k=0;k { c = Convert.ToDateTime(lst[0]); result += CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(c.Month + (k+1)).Substring(0, 3) + "" + Convert.ToString(c.Year).Substring(2,2) + " " ; } } else { result += CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(c.Month).Substring(0, 3) + "" + Convert.ToString(c.Year).Substring(2, 2) + " " ; } } Console.Write(result); Console.Read(); } } }

Sreekanth Reddy · Answer

DECLARE @Table TABLE (Col1 varchar(max)) DECLARE @date datetime2 = '2018-07-01'; WHILE @date BEGIN DECLARE @monthname varchar(20); DECLARE @dateis varchar(20); SET @monthname = cast( FORMAT(@date, 'MMMM') as varchar(max)) SET @dateis = cast(FORMAT(@date, 'dd') as varchar(max)) Insert into @Table values(@monthname + '-' + @dateis ) SET @date = DATEADD(day,1,@date); END select * from @Table