Get all Records between start date and end date [Asp.Net C# Web Forms]

I Have Table Named Employees_Attendance

{
Attend_ID  | EMPloyee_Name | Employee_Department | From_date  | To_date | Employee_ID

Employee_ID is Forigen_key To Employees Table Employee_ID Primary_Key
}

i am trying to fetch all records from Employees_attendance depends on $Start_date and $End_date
$Start_date is Text-Box Control Get his Text From Calendar Control
{<asp:TextBox ID="TextFrom" runat="server"></asp:TextBox> }
And $End_date as Same as $Start_date

My Issue:
when i search by $Start_date and $end_date the Returned Record are Match the $start_date only.

My desired result:
I need To get all Records for Specific Employee Depends on the Date in Employees_attendance_tbl
Between $Start_date that got it from the Text-Box as i mentioned above
And $end_date That got it from Text box Too

Please Take a look at my Stored Procedure

{
USE [ASPCRUD]
GO
/****** Object:  StoredProcedure [dbo].[DailyReportInfo]    Script Date: 11/23/2020 4:32:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[DailyReportInfo]

@Day_From  datetime,
@Day_To datetime
AS
BEGIN
select Emp_Attend.Emp_Name, Emp_Attend.Dep_Name , Emp_Attend.Day_From , Emp_Attend.Day_To  , Emp_Attend.Day_Type
from Emp_Attend 
LEFT JOIN Employees on Emp_Attend.Emp_ID = Employees.EmpID
WHERE
 CONVERT(date,Emp_Attend.Day_From) >= CONVERT(date,@Day_From) AND  CONVERT(date,Emp_Attend.Day_From) <= CONVERT(date,@Day_From) OR
 
 CONVERT(date,Emp_Attend.Day_To) >= CONVERT(date,@Day_To) AND  CONVERT(date,Emp_Attend.Day_To) <= CONVERT(date,@Day_To)
END
}

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Ok, so you want ANY attendance date between the given start/end date.

this is simple.

A collision occurs based on this logic:

RequestStartDate <= EndDate 
and 
RequestEndDate >= StartDate

The above will find ALL AND ANY overlaps.

So if the attendance record brackets the start end (is earlier and later), then a match occurs.

And if any request date falls in the range, then again a match occurs.

And if any request date overlaps? Then again a match occurs with the above SIMPLE condition.

so, lets build a form.

Drop in label + text box. Set text box format as date.
Cut + paste (start + end)

Drag gridview on form. So far? Less then 2 minutes time spend here.

So, we have this:

        <asp:Label ID="Label2" runat="server" Text="End Date" Style="margin-left:20px"></asp:Label>
        <asp:TextBox ID="txtRequestEnd" runat="server" Style="margin-left:20px" TextMode="Date"></asp:TextBox>

        <asp:Button ID="btnSearch" runat="server" Text="Search" Style="margin-left:20px"/>
        <br />
        <br />

        <asp:GridView ID="GridView1" runat="server"></asp:GridView>

So, to fill a grid with the results of the stored procedure we can use this code behind the button:

  Using cmdSQL As New SqlCommand("GetBookings",
                                   New SqlConnection(GetConstr()))
        cmdSQL.CommandType = CommandType.StoredProcedure
        cmdSQL.Connection.Open()
        cmdSQL.Parameters.Add("@ReqStart", SqlDbType.Date).Value = txtRequestStart.Text
        cmdSQL.Parameters.Add("@ReqEnd", SqlDbType.Date).Value = txtRequestEnd.Text

        Dim rs As New DataTable
        rs.Load(cmdSQL.ExecuteReader)

        GridView1.DataSource = rs
        GridView1.DataBind()

    End Using

And the results are this:

My stored procedure is this:

ALTER PROCEDURE [dbo].[GetBookings]
    @ReqStart datetime,
    @ReqEnd datetime
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * 
    FROM vBooking 
    WHERE @ReqStart <= ToDate 
      AND @ReqEnd >= FromDate
      AND HotelName IS NOT NULL
END

So, in your case, just use the above simple collision logic. Any overlap, for the start, or the end, or even a range that falls inside of your Request start + end will work for the above.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0