sql server optional parameters: syntax for between clause
@FromDate datetime = null
@ToDate datetime = null
SELECT * FROM TABLE
WHERE ....
AND [PI].Date BETWEEN @FromDate AND @ToDate
When any date is null, the record开发者_开发知识库s 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.
Try something like
SELECT * FROM TABLE
WHERE ....
AND [PI].Date BETWEEN ISNULL(@FromDate, [PI].Date) AND ISNULL(@ToDate, [PI].Date)
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'))
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
SELECT * FROM [PI]
WHERE ([PI].Date >= @FromDate OR @FromDate IS NULL)
AND ([PI].Date <= @ToDate OR @ToDate IS NULL)
精彩评论