title: Conditional Where clause in SQL query if one or both date range parameters are null
body: <p>I am not sure if I am asking the right question here, but I will try to explain what I need. I need to write a SQL query with a <code>@StartDate</code> and an <code>@EndDate</code> parameter.</p>
  <p>If both parameters have a value, then my <code>where</code> clause will need to include:</p>
  <pre><code>WHERE CAST(CreatedDate AS date) BETWEEN @StartDate AND @EndDate
  </code></pre>
  <p>If <code>@EndDate</code> is null, then it should be:</p>
  <pre><code>WHERE CAST(CreatedDate AS date) = @StartDate
  </code></pre>
  <p>If both or null then the date should not be a part of the WHERE clause at all.</p>
  <p>I have tried a couple of things like:</p>
  <pre><code>DECLARE @StartDate varchar(20) = '12-02-2020'
  DECLARE @EndDate varchar(20) = ''
  
  SELECT *
  FROM [dbo].[tbl_Support_Requests]
  WHERE 
      CAST(CreatedDate AS date) &gt;= ISNULL(@StartDate, CreatedDate)
      AND CAST(CreatedDate AS date) &lt;= ISNULL(@EndDate, CreatedDate)
  </code></pre>
  <p>and also:</p>
  <pre><code>DECLARE @StartDate varchar(20) = '12-02-2020'
  DECLARE @EndDate varchar(20) = ''
  
  SELECT *
  FROM [dbo].[tbl_Support_Requests]
  WHERE (@EndDate IS NULL 
         AND (CAST(CreatedDate AS date) &gt;= @StartDate) 
              OR (@EndDate IS NOT NULL 
                  AND CAST(CreatedDate AS date) &gt;= @StartDate 
                  AND CAST(CreatedDate AS date) &lt;= @EndDate))
  </code></pre>
  <p>I am hoping there is a way to do this without the need for dynamic SQL. Any assistance is greatly appreciated.</p>