Calculate Number of days between two dates excluding holidays from Database using C# and VB.Net in ASP.Net

Last Reply one year ago By pandeyism

Posted one year ago

Hi Sir

I am using the code from pandeyism

Calculate Number of days between two dates excluding holidays using C# and VB.Net in ASP.Net

How can fecth the data from mssql database for calculating the no of days between two selected date exclude the prescribed holiday?

Pls advice me

Thank you

Posted one year ago Modified on one year ago

Hey maideen,

Please refer below sample.

HTML

StartDate :
<asp:TextBox runat="server" ID="txtStartDate" />
<br />
EndDate :<asp:TextBox runat="server" ID="txtEndDate" />
<br />
<asp:Button ID="Button1" Text="Calculate" runat="server" OnClick="Calculate" />

Namespaces

C#

using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data.SqlClient

Code

C#

protected void Calculate(object sender, EventArgs e)
{
    List<DateTime> holidays = new List<DateTime>();
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT HoliDayDate FROM HolidayList WHERE HoliDayDate BETWEEN @Date1 AND @Date2", con))
        {
            cmd.Parameters.AddWithValue("@Date1", txtStartDate.Text);
            cmd.Parameters.AddWithValue("@Date2", txtEndDate.Text);
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    holidays.Add(Convert.ToDateTime(sdr[1]));
                }
            }
            con.Close();
            int days = 0;
            DateTime startDate = Convert.ToDateTime(txtStartDate.Text);
            DateTime endDate = Convert.ToDateTime(txtEndDate.Text);

            for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
            {
                if (startDate.DayOfWeek != DayOfWeek.Saturday && startDate.DayOfWeek != DayOfWeek.Sunday && !holidays.Contains(date))
                {
                    days++;
                }
                startDate = startDate.AddDays(1);

            }
            Response.Write("Number of days between " + Convert.ToDateTime(txtStartDate.Text).ToShortDateString() + " and "
                    + Convert.ToDateTime(txtEndDate.Text).ToShortDateString() + " excluding special holiday is " + days.ToString());

        }
    }
}

VB.Net

Protected Sub Calculate(ByVal sender As Object, ByVal e As EventArgs)
    Dim holidays As List(Of DateTime) = New List(Of DateTime)()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT HoliDayDate FROM HolidayList WHERE HoliDayDate BETWEEN @Date1 AND @Date2", con)
            cmd.Parameters.AddWithValue("@Date1", txtStartDate.Text)
            cmd.Parameters.AddWithValue("@Date2", txtEndDate.Text)
            con.Open()

            Using sdr As SqlDataReader = cmd.ExecuteReader()

                While sdr.Read()
                    holidays.Add(Convert.ToDateTime(sdr(1)))
                End While
            End Using

            con.Close()
            Dim days As Integer = 0
            Dim startDate As DateTime = Convert.ToDateTime(txtStartDate.Text)
            Dim endDate As DateTime = Convert.ToDateTime(txtEndDate.Text)
            Dim date1 As DateTime = startDate

            While date1 <= endDate

                If startDate.DayOfWeek <> DayOfWeek.Saturday AndAlso startDate.DayOfWeek <> DayOfWeek.Sunday AndAlso Not holidays.Contains(date1) Then
                    days += 1
                End If

                startDate = startDate.AddDays(1)
                date1 = date1.AddDays(1)
            End While

            Response.Write("Number of days between " & Convert.ToDateTime(txtStartDate.Text).ToShortDateString() & " and " + Convert.ToDateTime(txtEndDate.Text).ToShortDateString() & " excluding special holiday is " & days.ToString())
        End Using
    End Using
End Sub

Screenshot