Calculate Dates between 2 dates

Question

I have a start date and an end date.

Start Date: 14/06/2018
End Date: 14/06/2020

The user will fill in how many reviews they want to do for the 2 years.

I have calculated the date difference in weeks for the start and end date.

So, in this case, it would be 105 weeks.

Let's say the user wants to do a review every 10 weeks.

So, I divide the 105 weeks / how many reviews they want to do which = 10.5.

I would like to insert a record into my database 10 times and have the dates spread equally between the start date and end date.

How would I do this?

UPDATE EDIT:

        double test;

        test = (double)Convert.ToInt32(hdnUIOID.Value) / (double)Convert.ToInt32(txtEvery.Text);

        string constr = ConfigurationManager.ConnectionStrings["EBSLIVE"].ConnectionString;
        SqlConnection con = new SqlConnection(constr);
        SqlCommand cmd = new SqlCommand();


        var startDate = new DateTime(2018, 6, 14);
        var endDate = new DateTime(2020, 6, 14);
        var howManyReviews = test;
        // Calculate how many days between revisions
        var days = (endDate - startDate).Days / (howManyReviews - 1);
        // Calculate dates of revisions
        var revisionDates = new List<DateTime>() { startDate };
        while ((startDate = startDate.AddDays(days)) < endDate)
            revisionDates.Add(startDate);


        for (int i = 0; i < test; i++)
        {
            try
            {
                string strStudentStatus = "INSERT INTO EF_REVIEWDATES(REVIEW_DATE) VALUES "+ string.Join(",", Convert.ToDateTime(revisionDates.Select(dt => "('" + dt.ToString("yyyy-MM-dd") + "')")));
                cmd.CommandText = strStudentStatus.ToString();
                cmd.Parameters.Clear();

                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
            }

            catch (SqlException ex)
            {
                string errorMsg = "Error in Insert";
                errorMsg += ex.Message;
                throw new System.Exception(errorMsg);
            }
            finally
            {
                con.Close();

                Response.Redirect(Request.RawUrl);
            }
        }

Provide the code you have so far. Are you ding this in C# or SQL? — Matt, Sep 13 '19 at 08:30
Whichever is easiest and most efficient to do it in. But i still need to insert it into a database. — , Sep 13 '19 at 08:32
Yes, that's also trivial to do https://stackoverflow.com/questions/19956533/sql-insert-query-using-c-sharp — Liam, Sep 13 '19 at 08:33
I have already calculated the date diff between 2 dates. I just need to insert lets say 10 records over the course of 2 years. — , Sep 13 '19 at 08:33
use _.AddDays_ to StartDate and iterate 10 times. DateTime.Now.AddDays(10.5) — Krishna Varma, Sep 13 '19 at 08:36
https://stackoverflow.com/questions/1847580/how-do-i-loop-through-a-date-range, With Jon misc util that includes step. And https://stackoverflow.com/questions/3738748/create-an-array-or-list-of-all-dates-between-two-dates — xdtTransform, Sep 13 '19 at 08:38
Seems to be a job for [NCronTab](https://github.com/atifaziz/NCrontab). If you provide the desired cron expression and a start date it will give you all occurences. — Oliver, Sep 13 '19 at 08:41
@Oliver Although is useful knowing of NCrontab, the question if how to insert a bunch of data, not executing something periodically. — Cleptus, Sep 13 '19 at 08:43
I have added an alternative, more SQL oriented, approach of your problem. Probably is not what you need, because you have a mixed approach. — Cleptus, Sep 13 '19 at 09:08
Question goes all other the place. Please do not, never, edit answer into your question. It's invalidating other answer as the code now don't have the same behavior. — xdtTransform, Sep 13 '19 at 09:12
Sorry @xdtTransform, I'm new to this. I won't do it next time. Thanks for the info. — , Sep 13 '19 at 09:13
I will recommend always going back to [ask] and [mcve] any time you post a question. I still do and feels I will always use it as a guideline, even for professional mail. — xdtTransform, Sep 13 '19 at 09:17

Michał Turczyn · Answer 1 · 2019-09-13T08:53:56.783

1

Try below code (explanation in comments):

  var startDate = new DateTime(2018, 6, 14);
  var endDate = new DateTime(2020, 6, 14);
  var howManyReviews = 10;
  // Calculate how many days between revisions
  var days = (endDate - startDate).Days / (howManyReviews-1);
  // Calculate dates of revisions
  var revisionDates = new List<DateTime>() { startDate };
  while ((startDate = startDate.AddDays(days)) < endDate)
    revisionDates.Add(startDate);

To use it in insert statement you can use:

string strStudentStatus = "INSERT INTO REVIEWDATES(REVIEW_DATE) VALUES " 
  + string.Join(",", revisionDates.Select(dt => "('" + dt.ToString("yyyy-MM-dd") + "')"))

edited Sep 13 '19 at 08:53

answered Sep 13 '19 at 08:39

Michał Turczyn

32,028
14
47
69

This seems to work how but how would I integrate this with my insert statement? – Sep 13 '19 at 08:49
With this i want to insert via datetime not an vnarchar and how would i make it so it excludes weekends? – Sep 13 '19 at 09:20
@JoshSando There are no "difference without weekends" functions neither in .net nor in tsql. The solutions usually loop the data and check for saturdays and sundays. [.net](https://www.codeproject.com/Questions/311630/Get-date-range-excluding-weekends) example, [sql](https://forums.asp.net/t/1562092.aspx?Difference+between+two+dates+Excluding+weekends+) example – Cleptus Sep 13 '19 at 09:50

score 0 · Answer 2 · answered Sep 13 '19 at 08:38

I am not sure if this would work for you but I had something similar. So what you need to do is have a for loop, for((int i = 0; i < howmanylines; i++) and in your forloop, add the appropriate fields into the database and on the time fields, have something like this;

et_.time_in = new TimeSpan(ftime.Hour, ftime.Minute, ftime.Second);
                ftime = ftime.AddMinutes(increment);
                et_.time_out = new TimeSpan(ftime.Hour, ftime.Minute, ftime.Second);

Nkplizz · Answer 3 · 2019-09-13T08:54:27.933

0

you can try something like that in SQL:

declare
@startdate date ='14/06/2018',
@enddate date = '14/06/2020',

@weekInterval int = 10,
@Datei date 

set @Datei = @startdate

while @Datei<@enddate and DATEDIFF(week,@Datei,@enddate)>@weekInterval
begin
set @Datei =   DATEADD(week,@weekInterval,@Datei)
print @Datei
end

edited Sep 13 '19 at 08:54

answered Sep 13 '19 at 08:47

Nkplizz

1
1

score 0 · Answer 4 · answered Sep 13 '19 at 08:58

You can code it using c# or doing a full SQL query, using SQL you can either do loops or retrieve all the data at the same time.

This answer addresses a full SQL approach, avoids loops, and gets the data to be inserted.

It does generate a recursive CTE to get the amount the days in the interval. It then divides each row to an specific amount of days and checks the modulus to filter only those days that match the interval desired.

declare @startDate datetime = '20190101', @endDate datetime = '20190301', @amount int = 7

;with recursivecte as
(
    select @startDate as [day]
    union all select DATEADD(day,1, [day]) as [day] from recursivecte where [day] <= @endDate
)
select allDays.[day] from
(
    select [day], ROW_NUMBER() over(order by [day]) % @amount as modulus from recursivecte
) allDays where modulus = 1

Calculate Dates between 2 dates

4 Answers4