Increment DateTime value based on total number of days

Question

When a user adds a class they choose the start date and the end date of the class. I have been able to calculate the value between the end date and start date. I was able to create a For loop to insert a row into the Class table for each individual day the class is on. E.g 01/03/17 - 05/03/17 runs for 5 days so it adds in 5 total rows to the Class table.

Currently for each row it adds in the start date as 01/03/2017 00:00:00 and end date 05/03/2017 00:00:00.

For each row I want to increment the date by 1 until the end date has been reached

For example:

01/03/2017 00:00:00  
02/03/2017 00:00:00   
03/03/2017 00:00:00   
04/03/2017 00:00:00   
05/03/2017 00:00:00

Below is my code for my For loop:

  public class UpdateTimetable
  {
    internal void insert_days(int moduledata, int? recurrencedata, DateTime startdatedata, DateTime enddatedata, int classtypedata, int roomcodedata, int starttimedata, int endtimedata, string totalday)
    {
        int tdays = Convert.ToInt16(totalday);
        for (int i = 1; i <= tdays; i++)
        {
            string connectionString = WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

            SqlConnection myConnection = new SqlConnection(connectionString);

            myConnection.Open();

            string query = "INSERT INTO Class (ModuleId, ClassTypeId,  ClassScheduleStartTimeId, ClassScheduleEndTimeId, RoomCodeId, StartTime, EndTime, RecurrenceId) VALUES (@moduledata, @classtypedata, @starttimedata, @endtimedata, @roomcodedata, @startdatedata, @enddatedata,  @recurrencedata)";
            SqlCommand myCommand = new SqlCommand(query, myConnection);

            //myCommand.Parameters.AddWithValue("@daydata", DayId);
            myCommand.Parameters.AddWithValue("@moduledata", moduledata);
            myCommand.Parameters.AddWithValue("@classtypedata", classtypedata);
            myCommand.Parameters.AddWithValue("@startdatedata", startdatedata);
            myCommand.Parameters.AddWithValue("@enddatedata", enddatedata);
            myCommand.Parameters.AddWithValue("@roomcodedata", roomcodedata);
            myCommand.Parameters.AddWithValue("@starttimedata", starttimedata);
            myCommand.Parameters.AddWithValue("@endtimedata", endtimedata);
            myCommand.Parameters.AddWithValue("@recurrencedata", recurrencedata);

            myCommand.ExecuteReader();
            myConnection.Close();
        }
    }

}

This is my code for calculating the total number of days between start and end date.

            int moduledata = Convert.ToInt32(ddlModule.Text);
            DateTime startdatedata = Convert.ToDateTime(txtstartdate.Text);
            DateTime enddatedata = Convert.ToDateTime(txtenddate.Text);
            int classtypedata = Convert.ToInt32(ddlClassType.Text);
            int roomcodedata = Convert.ToInt32(ddlRoomCode.Text);
            int starttimedata = Convert.ToInt32(ddlStartClassTime.Text);
            int endtimedata = Convert.ToInt32(ddlEndClassTime.Text);

            startdatedata = DateTime.Parse(txtstartdate.Text).Date;
            enddatedata = DateTime.Parse(txtenddate.Text).Date;

            TimeSpan totaldays = enddatedata - startdatedata;

            // This is rounding the TimeSpan to the day value only
            string totalday = ((int)Math.Round(totaldays.TotalDays, MidpointRounding.AwayFromZero)).ToString();

public class UpdateTimetable
{
 internal void insert_days(int moduledata, int? recurrencedata, DateTime startdatedata, DateTime enddatedata, int classtypedata, int roomcodedata, int starttimedata, int endtimedata, string totalday)
    {
        int tdays = Convert.ToInt16(totalday);

        DateTime startDate = DateTime.Now.AddDays(tdays);
        DateTime endDate = enddatedata;

        List<string> Dates = new List<string>();

        for (int i = 1; i <= endDate.Subtract(startDate).Days; i++)
        {
            Dates.Add(startDate.AddDays(i).ToString());
            string connectionString = WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

            SqlConnection myConnection = new SqlConnection(connectionString);

            myConnection.Open();

            string query = "INSERT INTO Class (ModuleId, ClassTypeId,  ClassScheduleStartTimeId, ClassScheduleEndTimeId, RoomCodeId, StartTime, EndTime, RecurrenceId) VALUES (@moduledata, @classtypedata, @starttimedata, @endtimedata, @roomcodedata, @startdatedata, @enddatedata,  @recurrencedata)";
            SqlCommand myCommand = new SqlCommand(query, myConnection);

            //myCommand.Parameters.AddWithValue("@daydata", DayId);
            myCommand.Parameters.AddWithValue("@moduledata", moduledata);
            myCommand.Parameters.AddWithValue("@classtypedata", classtypedata);
            myCommand.Parameters.AddWithValue("@startdatedata", startdatedata);
            myCommand.Parameters.AddWithValue("@enddatedata", enddatedata);
            myCommand.Parameters.AddWithValue("@roomcodedata", roomcodedata);
            myCommand.Parameters.AddWithValue("@starttimedata", starttimedata);
            myCommand.Parameters.AddWithValue("@endtimedata", endtimedata);
            myCommand.Parameters.AddWithValue("@recurrencedata", recurrencedata);

            myCommand.ExecuteReader();
            myConnection.Close();
        }
    }

}

Answer 1

No need for loops, you can generate the values like so:

rextester demo: http://rextester.com/QDQKX23942

declare @fromdate date = '20170301'
declare @thrudate date = '20170307'

;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1) 
      [Date]=convert(datetime,dateadd(day, row_number() over (order by (select 1)) -1, @fromdate))
    from         n as deka
      cross join n as hecto      /* 100 days */
      cross join n as kilo     /* 2.73 years */
      --cross join n as [tenK]    /* 27.3 years */
   order by [Date]
)

select StartTime=dateadd(hour,9,d.date), EndTime=dateadd(hour,10,d.date)
from dates d

returns:

+---------------------+---------------------+
|      StartTime      |       EndTime       |
+---------------------+---------------------+
| 01.03.2017 09:00:00 | 01.03.2017 10:00:00 |
| 02.03.2017 09:00:00 | 02.03.2017 10:00:00 |
| 03.03.2017 09:00:00 | 03.03.2017 10:00:00 |
| 04.03.2017 09:00:00 | 04.03.2017 10:00:00 |
| 05.03.2017 09:00:00 | 05.03.2017 10:00:00 |
| 06.03.2017 09:00:00 | 06.03.2017 10:00:00 |
| 07.03.2017 09:00:00 | 07.03.2017 10:00:00 |
+---------------------+---------------------+

Or better yet, use a Calendar table:

Calendar and Numbers table references:

• Generate a set or sequence without loops - Aaron Bertrand
• The "Numbers" or "Tally" Table: What it is and how it replaces a loop - Jeff Moden
• Creating a Date Table/Dimension in SQL Server 2008 - David Stein
• Calendar Tables - Why You Need One - David Stein
• Creating a date dimension or calendar table in SQL Server - Aaron Bertrand
• TSQL Function to Determine Holidays in SQL Server - Tim Cullen
• F_TABLE_DATE - Michael Valentine Jones

---

To combine this with your existing insert statement, you could use a query like so, no loop needed.

;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @startdatedata,@enddatedata)+1) 
      [Date]=convert(datetime,dateadd(day, row_number() over (order by (select 1)) -1, @startdatedata))
    from         n as deka
      cross join n as hecto 
      cross join n as kilo  
   order by [Date]
)

insert into Class (ModuleId , ClassTypeId , ClassScheduleStartTimeId 
  , ClassScheduleEndTimeId , RoomCodeId , StartTime , EndTime , RecurrenceId ) 
select 
   @moduledata
 , @classtypedata
 , @starttimedata
 , @endtimedata
 , @roomcodedata
 , StartTime= d.Date + s.StartTime
 , EndTime  = d.Date + e.EndTime
 , @recurrencedata
from dates d
  cross apply (
    select StartTime = convert(datetime,ClassTime)
    from ClassSchedule 
    where ClassScheduleId = @starttimedata
    ) as s
  cross apply (
    select EndTime = convert(datetime,ClassTime)
    from ClassSchedule 
    where ClassScheduleId = @endtimedata
    ) as e

In this example the @starttimedata and @endtimedata would be integers that represent the hour. If you need add by the minute, you can switch to dateadd(minute....

There are other ways to add time to a date, but it depends on what types your parameters are and what values you are sending.

Answer 2

Finally I think i found what you want. Look at below code please:

        DateTime startDate = DateTime.Now.AddDays(-5); //5 days ago.
        DateTime endDate = DateTime.Now;

        List<string> Dates = new List<string>();

        for (int i = 0; i <= endDate.Subtract(startDate).Days; i++)
        {
            Dates.Add(startDate.AddDays(i).ToString()); //sample!
            //if enddatedata is the date that must icrease and startDate is the first Date:
            enddatedata = startDate.AddDays(i);

            // SQL insert HERE
        }

        //Dates:
        //2017-02-26 5:45:25 PM
        //2017-02-27 5:45:25 PM
        //2017-02-28 5:45:25 PM
        //2017-03-01 5:45:25 PM
        //2017-03-02 5:45:25 PM
        //2017-03-03 5:45:25 PM

Answer 3

If your question is how to get a new date object from a given one plus n days you can do

DateTime dt = someOldDt.AddDays(n);