Optional sql server options: syntax between conditions

Question

@FromDate datetime = null
@ToDate datetime = null

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

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

I thought about this:

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

Thank.

Answer 1

Try something like

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

Answer 2

you can always specify date values ​​up to some extreme value and make sure the 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'))

Answer 3

- :

@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

Answer 4

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