Get a List/DataTable of Months/Years between two Dates

Background

There is often a need to get a List/Datatable between two supplied dates.

Two days ago I needed to make a join between two Datatables so I thought I must share it among developers.

A scenario is that there is a table named user with a registration date. What I need to do is to create a report that will display the record for the users registered every month of this year. What I did was I fetched the data from the database that is like the following.

Table in my database

Table A

UserId UserFname UserCreatedDate

Table B

Months NoOfUsers

Now, I need to display a report for the users registered between 01/08/2013 and 01/03/2014.

So, I created a Datatable for all the months between those dates.

For this given below.

Just created a class.

public class BhanuHelper
{
public static Array GetMonths(DateTime date1, DateTime date2)
{
//Note - You may change the format of date as required.
return GetDates(date1, date2).Select(x => x.ToString("MMMM yyyy")).ToArray();
}
public static IEnumerable<DateTime> GetDates(DateTime date1, DateTime date2)
{
while (date1 <= date2)
{
yield return date1;
date1 = date1.AddMonths(1);
}
if (date1 > date2 && date1.Month==date2.Month)
{
// Include the last month
yield return date1;
}
}
public static DataTable ToDataTable<T>( IList<T> data)
{
PropertyDescriptorCollection props =
TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
for (int i = 0; i < props.Count; i++)
{
PropertyDescriptor prop = props[i];
table.Columns.Add(prop.Name, prop.PropertyType);
}
object[] values = new object[props.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
return table;
}
}

Implementation of above class

BhanuHelper.ToDataTable(BhanuHelper.GetMonths(Convert.ToDateTime("First date string"),Convert.ToDateTime("second date string")));

And the output will be like below:

Table C

Months

Months NoOfUser

I hope this article is useful for all readers, if you have any suggestion then please contact me including beginners also.

3 Comments

Join the conversation! Your thoughts help the community grow.

Jiyaul Mustapha7y ago
DECLARE @monthfrom int=null,@yearfrom int=null, @monthto int=null, @yearto int=null, @firstdate DATE=null, @lastdate DATE=null SELECT @firstdate=DATEADD(month,@monthfrom-1,DATEADD(year,@yearfrom-1900,0)) /*Setting First Date using From Month & Year*/ SELECT @lastdate= DATEADD(day,-1,DATEADD(month,@monthto,DATEADD(year,@yearto-1900,0)))/*Setting Last Date using From Month & Year*/ SELECT * FROM tbl_Record WHERE (DATEADD(yy, Year - 1900, DATEADD(m, Month - 1, 1 - 1)) BETWEEN CONVERT(DATETIME, @firstdate, 102) AND CONVERT(DATETIME, @lastdate, 102))
Zuhaib Mohamed8y ago
Hi When i using the class getting an error like "the type arguments for method .Todatatable <T>(IList<T>) cannot be inferred from the usage . try specifying the type arguments explicitly"
Bhanu Pratap Singh12y ago
Dear Vithal please delete this comment from here, i will do as i messaged you ok.