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

    1                                   A                                  13/08/2013
    2                                   B                                  13/10/2013
    3                                   C                                  13/02/2014

    The fetched data was like:

    Table B

    Months                 NoOfUsers

    0 82013                         1
    1 02013                         1
    0 22014                         1
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.
  1. public class BhanuHelper  
  2. {  
  3.   
  4.    public static Array GetMonths(DateTime date1, DateTime date2)  
  5.    {  
  6.       //Note - You may change the format of date as required.  
  7.       return GetDates(date1, date2).Select(x => x.ToString("MMMM yyyy")).ToArray();  
  8.    }  
  9.   
  10.   
  11.    public static IEnumerable<DateTime> GetDates(DateTime date1, DateTime date2)  
  12.    {  
  13.         while (date1 <= date2)  
  14.         {  
  15.              yield return date1;  
  16.              date1 = date1.AddMonths(1);  
  17.         }  
  18.         if (date1 > date2 && date1.Month==date2.Month)  
  19.         { 
  20.             // Include the last month  
  21.                 yield return date1;  
  22.         }  
  23.    }  
  24.    public static DataTable ToDataTable<T>( IList<T> data)  
  25.    {  
  26.         PropertyDescriptorCollection props =  
  27.         TypeDescriptor.GetProperties(typeof(T));  
  28.         DataTable table = new DataTable();  
  29.         for (int i = 0; i < props.Count; i++)  
  30.         {  
  31.              PropertyDescriptor prop = props[i];  
  32.              table.Columns.Add(prop.Name, prop.PropertyType);  
  33.         }  
  34.         object[] values = new object[props.Count];  
  35.         foreach (T item in data)  
  36.         {  
  37.             for (int i = 0; i < values.Length; i++)  
  38.             {  
  39.                  values[i] = props[i].GetValue(item);  
  40.             }  
  41.             table.Rows.Add(values);  
  42.         }  
  43.         return table;  
  44.    }  
  45. }  
Implementation of above class
  1. BhanuHelper.ToDataTable(BhanuHelper.GetMonths(Convert.ToDateTime("First date string"),Convert.ToDateTime("second date string")));  
And the output will be like below:
    Table C

    Months

    082013
    092013
    102013
    112013
    122013
    012014
    022014
    032014

    To fulfil my requirement finally, I did like below.
     
      Now, I just did a Left Join of my two Datatables and got the desired output.

    Months                   NoOfUser

    082013                          1
    092013                          0
    102013                          1
    112013                          0
    122013                          0
    012014                          0
    022014                          1
    032014                          0
I hope this article is useful for all readers, if you have any suggestion then please contact me including beginners also.
Ebook Download
View all
Learn
View all