How to select date range in Microsoft SQL Server?

Question

I'm using (MVC)

Suppose I'm sending this date range 01/04/2020 to 04/04/2020 from the entry side from my Microsoft SQL Server.

I need to create a selection procedure to select the data from that date range. I created my query to select the details from the following tables but I hardcoded the dates.

But I need to create my procedure to accept any date range from an interface where the user input date range changes always and my query to accept it and return the data back to the MVC program.

Can anyone help me to do this? I'm still learning.

GO

--CREATE PROCEDURE [dbo].[ReservationEnquirySelect_M_Select]'2020-04-01' AND '2020-04-04'
CREATE PROCEDURE [dbo].[ReservationEnquirySelect_M_Select]
AS
BEGIN
    SELECT BRD.ReservationDate
        ,BRH.ReservationNo
        ,BCP.Name1
        ,BCP.Name2
        ,VD.Name
        ,PT.Name
        ,BRH.IsCompleted
    FROM BanquetReservationDetail BRD
    INNER JOIN ReservationHeader BRH ON BRD.ReservationHeaderID = BRH.ReservationHeaderID
    INNER JOIN CustomerProfile BCP ON BRH.CutomerProfileID = BCP.CustomerProfileID
    INNER JOIN VenueDetails VD ON BRD.VenueID = VD.VenueID
    INNER JOIN PackageTypes PT ON BRD.PackageTypeID = PT.PackageTypeID
    WHERE ReservationDate BETWEEN '2020-04-01'
            AND '2020-04-04'
END

ReservationDate BETWEEN 2020-04-01 and 2020-04-04. Instead of this, the query should accept a date range from the user.

Add @StartDate and @EndDate parameters to the stored procedure header, specify those parameters in the `WHERE` clause, and specify the parameter values in the calling app. — Dan Guzman, Apr 18 '20 at 16:57

score 3 · Accepted Answer · answered Apr 18 '20 at 16:56

You can create your procedure like this below one. There are different ways to call this stored procedure in MVC control.

Here is one of that way to call stored procedure in MVC. You can also use Entity Framework to consume your store procedure.

CREATE PROCEDURE [dbo].[ReservationEnquirySelect_M_Select] @FromDt DATE
    ,@ToDt DATE
AS
BEGIN
    SELECT BRD.ReservationDate
        ,BRH.ReservationNo
        ,BCP.Name1
        ,BCP.Name2
        ,VD.Name
        ,PT.Name
        ,BRH.IsCompleted
    FROM BanquetReservationDetail BRD
    INNER JOIN ReservationHeader BRH ON BRD.ReservationHeaderID = BRH.ReservationHeaderID
    INNER JOIN CustomerProfile BCP ON BRH.CutomerProfileID = BCP.CustomerProfileID
    INNER JOIN VenueDetails VD ON BRD.VenueID = VD.VenueID
    INNER JOIN PackageTypes PT ON BRD.PackageTypeID = PT.PackageTypeID
    WHERE ReservationDate BETWEEN @FromDt
            AND @ToDt
END

How to select date range in Microsoft SQL Server?

1 Answers1