You have an Azure SQL database that contains a table named dbo.

The correct SARGable pattern for filtering a single calendar day is to use a half-open date range :

o.CreateDate > = @StartDate

AND o.CreateDate < @EndDate

with:

SET @EndDate = DATEADD(day, 1, @StartDate)

This is the correct design because it keeps the function off the column and applies it only to the parameter. That allows SQL Server and Azure SQL to use an index on CreateDate efficiently, which is the key requirement for a SARGable predicate. Microsoft documents DATEADD as the standard function for adding one day to a date value, which makes it the right way to derive the exclusive upper boundary for the next day.

The incorrect choices are the ones that wrap CreateDate in CONVERT(...), because expressions like:

CONVERT(char(10), CreateDate, 121) = ...

make the predicate non-SARGable and typically prevent efficient seeks on an index over CreateDate.

So the completed procedure is:

CREATE PROCEDURE dbo.usp_SearchOrders

@StartDate date

AS

BEGIN

SET NOCOUNT ON;

DECLARE @EndDate date;

SET @EndDate = DATEADD(day, 1, @StartDate);

SELECT o.CreateDate,

o.OrderId,

o.ShipDate

FROM dbo.Orders AS o

WHERE o.CreateDate > = @StartDate

AND o.CreateDate < @EndDate;

END;