SQL date selection as NULL, After, Before, Between

Question

Requirement: Select by date as After, Before, Forward, or All if null

I am using SQL Server 2008

This is my attempt, but I get syntax errors for code that is actually used out of the case.

• Is there a better way?

• using case, what is the correct syntax?

  declare @StartDate datetime; declare @EndDate datetime; SET @EndDate = GETDATE(); SET @StartDate = DATEADD(year, -2, GETDATE()); select * from ArCustomer where CAST(Customer as int) > 1000 AND CASE WHEN @StartDate IS NOT NULL AND @EndDate IS NOT NULL THEN ArCustomer.DateLastSale BETWEEN @StartDate AND @EndDate WHEN @StartDate IS NULL AND @EndDate IS NOT NULL THEN ArCustomer.DateLastSale < @EndDate WHEN @StartDate IS NOT NULL AND @EndDate IS NULL THEN ArCustomer.DateLastSale > @StartDate END; 

Answer 1

Alternatively, you cannot restrict to the date parameter if it is NULL :

 SELECT * FROM ArCustomer ac WHERE CAST(ac.Customer as int) > 1000 AND (ac.DateLastSale >= @StartDate OR @StartDate IS NULL) AND (ac.DateLastSale <= @EndDate OR @EndDate IS NULL) 

Or ... you can handle NULL by treating it as the bottom or end date:

 SELECT * FROM ArCustomer ac WHERE CAST(ac.Customer as int) > 1000 AND ac.DateLastSale BETWEEN ISNULL(@StartDate, '1900-01-01') AND ISNULL(@EndDate, '9999-12-31') 

---

EDIT:

There may be a difference in execution plan between the two approaches, so you can try both methods and see if one of the others will execute ...

Answer 2

 WHERE CAST(Customer as int) > 1000 AND (@StartDate IS NULL OR @StartDate <= ArCustomer.DateLastSale) AND (@EndDate IS NULL OR ArCustomer.DateLastSale <= @EndDate) 

Answer 3

Please note that the query below should be * excluded and specific column names should be specified.

 declare @StartDate datetime; declare @EndDate datetime; SET @EndDate = GETDATE(); SET @StartDate = DATEADD(year, -2, GETDATE()); Declare @SQL Varchar(1000) Set @SQL = 'select ColumnName from ArCustomer where CAST(Customer as int) > 1000 AND' if(@StartDate IS NOT NULL AND @EndDate IS NOT NULL) Begin Set @SQL = @SQL + ' ArCustomer.DateLastSale BETWEEN ''' + Convert(varchar, @StartDate) + ''' AND ''' + Convert(varchar, @EndDate) + '''' End else if(@StartDate IS NULL AND @EndDate IS NOT NULL) Begin Set @SQL = @SQL + ' ArCustomer.DateLastSale < ''' + Convert(varchar, @EndDate) + '''' End else Set @SQL = @SQL + ' ArCustomer.DateLastSale > ''' + Convert(varchar, @StartDate) + '''' exec(@SQL) 

All cases are considered.