Stored procedure Date From and Date To
<p>I am trying to create a stored procedure for filtering orders. Basically the users have the option of filtering the order by date from and date to. So they can do search via date from, date to or use both if it makes sense?</p>

<p>Anyhow here is my SQL Server stored procedure so far </p>

<pre><code>ALTER PROCEDURE [dbo].[CN_GetOrderItemByCustID]
    @CustomerID int,
    @OrderItemWRClass varchar(max) = NULL,
    @OrderItemSKUName varchar(50) = NULL,
    @OrderItemDateFrom Datetime,
    @OrderItemDateTo Datetime
AS
BEGIN
    SET NOCOUNT ON;

    IF DATEDIFF(d, @OrderItemDateFrom, '01/01/1970') = 0 
       SET @OrderItemDateFrom = null 
    IF DATEDIFF(d, @OrderItemDateTo, '01/01/1970') = 0 
       SET @OrderItemDateTo = null 

    -- Insert statements for procedure here
    SELECT 
        COM_OrderItem.OrderItemID, COM_Order.OrderID, 
        COM_Order.OrderDate, COM_OrderItem.OrderItemUnitCount, 
        COM_OrderItem.OrderItemStatus, COM_OrderItem.OrderItemSKUNAME,
        COM_OrderItem.OrderItemSKUID
    FROM 
        COM_OrderItem
    INNER JOIN 
        COM_Order ON COM_Order.OrderID = COM_OrderItem.OrderItemOrderID
    WHERE  
        COM_Order.OrderCustomerID = @CustomerID 
        OR COM_OrderItem.OrderItemWRClass LIKE @OrderItemWRClass + '%' 
        OR COM_OrderItem.OrderItemSKUName LIKE @OrderItemSKUName + '%'
        OR CONVERT(VARCHAR, COM_Order.OrderDate, 120) LIKE @OrderItemDateFrom + '%' 
    ORDER BY 
        COM_Order.OrderDate DESC
</code></pre>

<p>However I am not sure on how to put the date from (<code>OrderItemDateFrom</code>) and date to (<code>OrderItemDateTo</code>) in the final SQL statement? </p>

<p>Should I be using <code>OR CONVERT(VARCHAR, COM_Order.OrderDate, 120) LIKE @OrderItemDateFrom + '%'</code> -- which gives me an error </p>

<blockquote>
  <p>Conversion failed when converting date and/or time from character string.</p>
</blockquote>

<p>I know in a normal SQL query I would use <code>Between OrderItemDateFrom and OrderItemDateTo</code></p>

<p>Thanks</p>