tsql between two dates which can be null [closed]

Question

I'm new in TSQL. I need to write a stored procedure which returns filtered records. So the user has two dates to enter. He may enter only one date, or two, or both, or any.

In stored proc I have two params @From, @To - date type. I need to search records.

If user didn't enter dates - I don't care about dates in the select query.

If the user entered 2 dates - I need to search with them inclusively.

If the user entered @From date - I need to search up to today inclusively.

If the user entered @To date - I need to search dates less than @To.

Thanks for your help.

Answer 1

SELECT ColumnList
FROM MyTable
WHERE
    (@FromDate IS NULL OR FromDateColumn >= @FromDate)
    AND (@ToDate IS NULL OR ToDateColumn <= @ToDate )

(But be aware that this can suffer from the effects of an unsuitable cached query plan due to parameter sniffing, especially if there is a large number of parameter conditions in the WHERE clause)

Answer 2

SELECT 
   Columns
FROM  
   Table
WHERE 
   DateColumn BETWEEN (CASE WHEN @FromDate IS NULL THEN DateColumn ELSE @FromDate END)
   AND (CASE WHEN @ToDate IS NULL THEN DateColumn ELSE @ToDate END)