SQL chooses between dates, but shows everything when empty and end date are empty
In SQL, I can use the '%' wildcard for the string, and the Select statement will show all records like
select * from tablea where title like '%'
My question is what if the start and end date is empty in the BETWEEN and AND functions in the Select statement.
Example
`select * from table where inputdate between '2014-01-01'` and '2014-01-31'
It works great in this statement because there is a start and end date.
What if there is no start and end date and I want to show all entries?
select * from table where inputdate between '%' and '%'
<--- doesn't work.
Many thanks.
source to share
You can write your query such that the column is inputdate
evaluated relative to the passed value, or if no value is passed to the @StartDate variable, just select the earliest possible date,
I chose 1900-01-01 as the earliest date, but depending on the column type, you can choose more appropriate start and end dates for the replacement.
DECLARE @StartDate DATE = '2014-01-01'
DECLARE @EndDate DATE = '2014-01-31'
select * from table
where inputdate >= COALESCE(@StartDate, '19000101')
and inputdate <= COALESCE(@EndDate , '99991231')
Dynamic SQL
Another better way to handle this kind of query with additional parameters would be using dynamic sql code like this ...
DECLARE @StartDate DATE = '2014-01-01'
DECLARE @EndDate DATE = '2014-01-31'
DECLARE @Sql NVARCHAR(MAX);
SET @Sql = N'SELECT * FROM Table 1 = 1 '
+ CASE WHEN @StartDate IS NOT NULL THEN
N' AND inputdate >= @StartDate ' ELSE N' ' END
+ CASE WHEN @EndDate IS NOT NULL THEN
N' AND inputdate <= @EndDate ' ELSE N' ' END
Execute sp_executesql @Sql
,N'@StartDate DATE, @EndDate DATE'
,@StartDate
,@EndDate
source to share