SQL Server可选参数:BETWEEN子句的语法

试试像这样的

SELECT * FROM TABLE 
WHERE .... 
AND [PI].Date BETWEEN ISNULL(@FromDate, [PI].Date) AND ISNULL(@ToDate, [PI].Date)

您始终可以将日期值默认为某个极值,并确保使用索引:

SELECT 
    * 
    FROM TABLE 
    WHERE .... 
        AND [PI].Date BETWEEN ISNULL(@FromDate,convert(datetime,'1/1/1800'))
                          AND ISNULL(@ToDate, convert(datetime,'1/1/2500'))

你可以试试这样的方法:

@FromDate datetime = null
@ToDate datetime = null

SELECT * FROM TABLE
WHERE ....
AND [PI].Date BETWEEN CASE WHEN @FromDate is null THEN '01/01/1901' ELSE @FromDate END AND 
CASE WHEN @ToDate is null THEN '12/31/9999' ELSE @ToDate END

高温高压

SELECT * FROM [PI]
WHERE ([PI].Date >= @FromDate OR @FromDate IS NULL)
AND ([PI].Date <= @ToDate OR @ToDate IS NULL)