CODEWITHSUNDEEP
sql-server-2008datestored-procedures
A.Goutam
A.Goutam

Reputation: 3494

How to use BETWEEN for datetime stored procedure

I am creating a stored procedure for searching. DateOrdered is column name in table of datetime type.

The problem is that I want to perform search on this column. The user can search on start date and end date. Also user can send null for any parameter, like start date or end date.

When the user will not send the start date or end date, I shall search on another option. My problem is that how can I handle this below is the query I tried, but without success

SELECT  
    @C_Order_ID = C_Order_ID 
FROM 
    C_Order  COrder
WHERE  
    (@AD_Org_ID IS NULL OR 
     COrder.AD_Org_ID IN (SELECT ID FROM fnSplitter(@AD_Org_ID)))  
    AND (@AD_Client_ID IS NULL OR 
         @AD_Client_ID IN (SELECT ID FROM fnSplitter(@AD_Client_ID)))  
    AND (@IsActive IS NULL OR COrder.IsActive = @IsActive) 
    AND (@startDate IS NULL OR 
         COrder.DateOrdered = @startDate BETWEEN @EndDate IS NULL 
          OR COrder.DateOrdered = @EndDate)

Thanks for your reply .

Upvotes: 1

Views: 95

Answers (3)

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You may try like this:

COrder.DateOrdered BETWEEN  @startDate AND @EndDate

So your query would be like

SELECT  @C_Order_ID= C_Order_ID FROM C_Order  COrder
WHERE  
     (@AD_Org_ID IS NULL OR COrder.AD_Org_ID IN (Select ID From fnSplitter(@AD_Org_ID)))  
 AND (@AD_Client_ID IS NULL OR @AD_Client_ID IN (Select ID From fnSplitter(@AD_Client_ID)))  
 AND (@IsActive IS NULL OR COrder.IsActive = @IsActive) 
 AND (@startDate IS NULL OR COrder.DateOrdered BETWEEN  isnull(@startDate,'') AND  isnull(@EndDate,''))

or better

SELECT  @C_Order_ID= C_Order_ID FROM C_Order  COrder
    WHERE  
         (@AD_Org_ID IS NULL OR COrder.AD_Org_ID IN (Select ID From fnSplitter(@AD_Org_ID)))  
     AND (@AD_Client_ID IS NULL OR @AD_Client_ID IN (Select ID From fnSplitter(@AD_Client_ID)))  
     AND (@IsActive IS NULL OR COrder.IsActive = @IsActive) 
     AND (CAST(@startDate AS DATE)  IS NULL OR (CAST(@startDate AS DATE) IS NULL OR CAST(COrder.DateOrdered AS DATE)  >=CAST(@startDate AS DATE))
    AND (CAST(@endDate AS DATE)  IS NULL OR CAST(COrder.DateOrdered AS DATE) <=CAST(@endDate AS DATE))

Upvotes: 1

GarethD
GarethD

Reputation: 69749

Aaron Bertrand has written a good article about using BETWEEN with date ranges - What do BETWEEN and the devil have in common?, this is worth a read. Also it looks like you are passing a comma separated list in a string, then splitting it with a function fnsplitter, you may want to consider using table-valued paramters

However, to actually answer your question you can use the above idea of not using between and change your query to:

AND (@startDate IS NULL OR COrder.DateOrdered >= @startDate)
AND (@EndDate IS NULL OR COrder.DateOrdered <= @EndDate)

So you have 4 permutations.

  1. Both @StartDate and @EndDate are null - returns all dates
  2. @StartDate is not null and @EndDate is null - returns all dates after @StartDate
  3. @StartDate is null and @EndDate is not null - returns all dates before @EndDate
  4. Both @StartDate and @EndDate are not null - returns all dates between @StartDate and @EndDate

Based on your comments on another answer, it sounds like you may be passing a date as the @EndDate parameter, e.g. 2014-08-28, but still want to include all records on that date, even if they have a time associated, e.g. 2014-08-28 12:00, this is exactly why BETWEEN is not used for date ranges, instead you need to use the less than operator and add 1 day to your End date:

AND (@startDate IS NULL OR COrder.DateOrdered >= @startDate)
AND (@EndDate IS NULL OR COrder.DateOrdered < DATEADD(DAY, 1, @EndDate))

Upvotes: 0

Raghu
Raghu

Reputation: 709

SELECT  @C_Order_ID= C_Order_ID FROM C_Order  COrder
WHERE  
 (@AD_Org_ID IS NULL OR COrder.AD_Org_ID IN (Select ID From fnSplitter(@AD_Org_ID)))  
 AND (@AD_Client_ID IS NULL OR @AD_Client_ID IN (Select ID From fnSplitter(@AD_Client_ID)))  
 AND (@IsActive IS NULL OR COrder.IsActive = @IsActive) 
 AND (@startDate IS NULL OR COrder.DateOrdered BETWEEN @startDate AND @EndDate
OR COrder.DateOrdered = @EndDate)

Upvotes: 0

Related Questions