sql server optional parameters: syntax for between

问题:

@FromDate datetime = null
@ToDate datetime = null

SELECT * FROM TABLE
WHERE ....
AND [PI].Date BETWEEN @FromDate AND @ToDate

When any date is null, the records are not displayed. What is the correct syntax so that I can get all records if any of the dates are null.

I have thought of this:

@FromDate datetime = '01/01/1901',
@ToDate datetime = '12/31/9999'

Thanks.

回答1:

Try something like

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

回答2:

you can always default the date values to some extreme value and make sure an index is used:

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

回答3:

You can try something like this:

@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

HTH

回答4:

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