Concatenate Months between two dates

Rameez Javed

8y794SQL

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar8y ago
Accepted 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)<= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@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.
+2
Rameez Javed8y ago
Suraj Kumar Thanks Buddy, Its works.
+1
Udit Chauhan8y ago
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< lst.Count;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();
}
}
}
+1
Sreekanth Reddy8y ago
DECLARE @Table TABLE (Col1 varchar(max))
DECLARE @date datetime2 = '2018-07-01';
WHILE @date < '2018-09-01'
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
-1