Check if the end date is getting overlapping with the existing start and end date

Question

I am having the following results in a datatable where I would like to validate the records to see if any over lapping exists with the given date from UI

When user tries to edit the first entry by giving a end date >= December 22 I would like to throw a validation saying over lap exists. Here is the sample code I am working out but didn't worked

public partial class Form1 : Form
{
    DataTable dt = new DataTable();
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        dt.Columns.Add("Id", typeof(int));
        dt.Columns.Add("StartDt", typeof(DateTime));
        dt.Columns.Add("EndDt", typeof(DateTime));

        dt.Rows.Add(1, new DateTime(2021, 11, 30), new DateTime(2021, 12, 14));
        dt.Rows.Add(2, new DateTime(2021, 12, 22), new DateTime(2022, 01, 05));
    }

    private void button1_Click(object sender, EventArgs e)
    {
        foreach (DataRow row in dt.Rows)
        {
            if (Convert.ToDateTime(row["StartDt"]).Date >= dateTimePicker1.Value.Date && Convert.ToDateTime(row["EndDt"]).Date <= dateTimePicker1.Value.Date)
            {

            }
        }
    }
}

When I select the date as 22 December 2021 as per the data it falls in 2nd row it should return false

Also any possible solution in linq with out looping data

Answer 1

Also any possible solution in linq with out looping data

If you're going to use LInQ on a datatable you really should make a strongly typed one. Using linq on weak types ones is an ugly, ugly affair

Do this:

• add a new DataSet type of file to your project. Call it something sensible, not DataSet1. If you're making an appointments app maybe call it SchedulerDS
• open it, and in the props grid set the name of the dataset to something nice like SchedulerDS. Right click the design surface and choose Add Datatable. Call it something sensible like Appointments
• right click the datatable and add an Id column (type int) and a StartDate and EndDate column (dates)
• save

Now you're ready to replace your weakly typed datatable with a strongly typed one. Golden rule: avoid ever using the .Rows property; just straight access your strongly typed datatable as if it were an array. As soon as you access Rows you're back in the world of pain that is weakly typed datatables

//yes
foreach(var row in datatable)

//no
foreach(DataRow row in datatable.Rows)

//yes
datatable.Where(...)

//heck no
datatable.Rows.Cast<DataRow>().Where(...)

Let us begin:

    SchedulerDS.AppointmentsDataTable dt = new SchedulerDS.AppointmentsDataTable();

    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        

        dt.AddAppointmentsRow(1, new DateTime(2021, 11, 30), new DateTime(2021, 12, 14));
        dt.AddAppointmentsRow(2, new DateTime(2021, 12, 22), new DateTime(2022, 01, 05));
    }

And you can linq for your "any rows overlapping?"

var d = dateTimePicker.Value.Date;
var anyOverlap = dt.Any(row => row.StartDate >= d && row.EndDate <= d);

return !anyOverlap; //if any overlap is true, validation should be false 

Answer 2

Long story short: if you want to find a value outside of your range, your have to check whether the value comes before the range OR after the range, rather than checking whether the values comes before AND after the range.

---

The correct range-checking logic (including dates or other sortable data) would be

Test, if actualValue is between borderStart and borderEnd in an inclusive range

borderStart <= actualValue && actualValue <= borderEnd

Test, if actualValue is between borderStart and borderEnd in an exclusive range

borderStart < actualValue && actualValue < borderEnd

Test, if actualValue is not between borderStart and borderEnd in an inclusive range

borderStart > actualValue || actualValue > borderEnd

Test, if actualValue is not between borderStart and borderEnd in an exclusive range

borderStart >= actualValue || actualValue >= borderEnd

Pick your poison.

Answer 3

Well, I notice you use >= dtPicker1 && <= dtPicker1. You might mean to change the second dtPicker1 to dtPicker2?

Edit: here is an SO answer on DataTable linq.