How to find missing Dates between two Dates using C# and VB.Net in ASP.Net

Last Reply 6 days ago By paulrajmca

Posted 11 days ago

hi Developers,

i want to get all Missing dates of two dates in asp.net C# code dynamically and i need to bind the dates. i have last Inserted record and Current date but i dont know how to bind the missing dates one by one.

this is my database Records
E001 07/08/2017
E001 08/08/2017
E001 11/08/2017

in this record 09/08/2017,10/08/2017 dates are missing i want to bind the two dates one by one.
if suppose my record is like
E001 01/08/2017
E001 11/08/2017

i want to bind the 10 missing dates of database.
friends if anyone of you know please suggest me to how i am done this task.

thanks with
Paul.S
"A man becomes what he thinks about"

 

Posted 11 days ago

Hi paulrajmca,

I have created a sample which full fill your requirement you need to modify the code according to your need.

C#

protected void Page_Load(object sender, EventArgs e)
{
    DataTable dtDatabase = new DataTable();
    dtDatabase.Columns.AddRange(new DataColumn[] { new DataColumn("EmployeeId", typeof(string)), new DataColumn("Date", typeof(DateTime)) });
    dtDatabase.Rows.Add("E001", "08/07/2017");
    dtDatabase.Rows.Add("E001", "08/08/2017");
    dtDatabase.Rows.Add("E001", "08/11/2017");
    if (dtDatabase.Rows.Count > 1)
    {
        DateTime endDate = Convert.ToDateTime(dtDatabase.Rows[dtDatabase.Rows.Count - 1]["Date"]);
        string employeeId = Convert.ToString(dtDatabase.Rows[dtDatabase.Rows.Count - 1]["EmployeeId"]);
        dtDatabase.Rows.RemoveAt(dtDatabase.Rows.Count - 1);
        dtDatabase.AcceptChanges();
        DateTime startDate = Convert.ToDateTime(dtDatabase.Rows[dtDatabase.Rows.Count - 1]["Date"]);

        int diff = endDate.Subtract(startDate).Days;
        if (diff > 1)
        {
            for (int i = 1; i < diff; i++)
            {
                dtDatabase.Rows.Add(employeeId, startDate.AddDays(i));
                dtDatabase.AcceptChanges();
            }
        }
        dtDatabase.Rows.Add(employeeId, endDate);
        dtDatabase.AcceptChanges();
    }
}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
	Dim dtDatabase As New DataTable()
	dtDatabase.Columns.AddRange(New DataColumn() {New DataColumn("EmployeeId", GetType(String)), New DataColumn("Date", GetType(DateTime))})
	dtDatabase.Rows.Add("E001", "08/07/2017")
	dtDatabase.Rows.Add("E001", "08/08/2017")
	dtDatabase.Rows.Add("E001", "08/11/2017")
	If dtDatabase.Rows.Count > 1 Then
		Dim endDate As DateTime = Convert.ToDateTime(dtDatabase.Rows(dtDatabase.Rows.Count - 1)("Date"))
		Dim employeeId As String = Convert.ToString(dtDatabase.Rows(dtDatabase.Rows.Count - 1)("EmployeeId"))
		dtDatabase.Rows.RemoveAt(dtDatabase.Rows.Count - 1)
		dtDatabase.AcceptChanges()
		Dim startDate As DateTime = Convert.ToDateTime(dtDatabase.Rows(dtDatabase.Rows.Count - 1)("Date"))

		Dim diff As Integer = endDate.Subtract(startDate).Days
		If diff > 1 Then
			For i As Integer = 1 To diff - 1
				dtDatabase.Rows.Add(employeeId, startDate.AddDays(i))
				dtDatabase.AcceptChanges()
			Next
		End If
		dtDatabase.Rows.Add(employeeId, endDate)
		dtDatabase.AcceptChanges()
	End If
End Sub

 

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html