How can I get around SQL Server - the value of the embedded table? The value of changing the program execution plan based on the parameters?

Question

Here's the situation:
I have a table value function with a datetime parameter, not to say tdf (p_date), which filters about two million rows, selecting those whose column date is less than p_date, and calculates some aggregated values ​​in other columns.
It works fine, but if p_date is a special function of a scalar value (returning the end of the day in my case), the execution plan changes, the request runs from 1 second to 1 minute.

Proof of concept table - 1K products, 2M lines:

CREATE TABLE [dbo].[POC](
    [Date] [datetime] NOT NULL,
    [idProduct] [int] NOT NULL,
    [Quantity] [int] NOT NULL
) ON [PRIMARY]

Table inline value function:

CREATE FUNCTION tdf (@p_date datetime)
RETURNS TABLE 
AS
RETURN 
(
    SELECT idProduct, SUM(Quantity) AS TotalQuantity,
         max(Date) as LastDate
    FROM POC
    WHERE (Date < @p_date)
    GROUP BY idProduct
)

Scalar value function:

CREATE FUNCTION [dbo].[EndOfDay] (@date datetime)
RETURNS datetime
AS
BEGIN
    DECLARE @res datetime
    SET @res=dateadd(second, -1,
         dateadd(day, 1, 
             dateadd(ms, -datepart(ms, @date),
                 dateadd(ss, -datepart(ss, @date),
                    dateadd(mi,- datepart(mi,@date),
                         dateadd(hh, -datepart(hh, @date), @date))))))
    RETURN @res
END

Request 1 - Works Great

SELECT * FROM [dbo].[tdf] (getdate())

: Stream 13% < --- 86%

2 -

SELECT * FROM [dbo].[tdf] (dbo.EndOfDay(getdate()))

: 4% < --- 12% < --- 86%

Answer 1

- .

TVF , ,

SELECT * FROM [dbo].[tdf] (getdate())

SELECT     idProduct, SUM(Quantity) AS TotalQuantity, max(Date) as LastDate
    FROM         POC
    WHERE     Date < getdate()
    GROUP BY idProduct

, SQL EOD (GETDATE()) . , SQL , .

, , , .

EOD:

DECLARE @eod datetime;
SET @eod = dbo.EndOfDay(getdate());
SELECT * FROM [dbo].[tdf] (@eod)

EOD:

DATEADD(second, -1, DATEADD(day, 1, (DATEDIFF(day, 0, @date))))

EDIT: ,

Answer 2

EndOfDay UDF UDF. :

UDF

UDF