Why hardcoded date filter works much better than dynamic parameter?
We cannot know for sure  without both execution plans. Below are three common reasons.

Filtered indexes

It could be the use of a filtered index such as:

        CREATE INDEX IX_Order_ProductCode_CustomerId_Filtered
        ON [dbo].[Order](ProductCode,CustomerId)
        INCLUDE(date_inserted)
        WHERE date_inserted &gt;= '2019-06-01';


This should pop up as an unmatched indexes warning in the execution plan. More on these here

Parameter sniffing

Another common reason would be parameter sniffing, where the value that runs with the stored procedure first / on plan creation is cached for following executions, until the plan is recompiled. Recompilation can for example happen due to statistics of one of the referenced tables being updated.

More on parameter sniffing here

If you want sql server to see the parameter as a hardcoded value then you would have to add OPTION(RECOMPILE) to the query.

Wrong datatypes

if the date_inserted column is a string datatype such as varchar() then your query would not be able to seek on the parameter. It would do an implicit conversion of the varchar() field to a date field on a residual predicate instead of a seek predicate.

The difference between below two queries could then  be immense

SELECT * FROM [Order] o 
WHERE o.date_inserted &lt; '2019-06-01' 

DECLARE @date date = '2019-06-01' 

SELECT * FROM [Order] o 
WHERE o.date_inserted &lt; @date



With more than 2 seconds execution time on the second query

Due to the residual predicate:



where the entire date_inserted column is converted to a date datatype before filtering.