title: Changing the date used within a query which has been provided by a parameter
body: <p>I am trying to create a query which will be used within a reporting dashboard. the query needs to pull a date from a parameter which is feed from a datetime control within the dash board. </p>
  
  <p>The issue i have is that the date time control will show the current date or can be changed using a drop down calender. I need the query to look at the date provided and perform a bit of an if statement. </p>
  
  <p>If the date provided is the same month as the current month. I need the date used within the query to be the last day of the previous month. ELSE it should use the last day of the month selected.</p>
  
  <p>I have tried several variations writing my code into the Where Clause but have so far not come to any success. Even just trying to get the query to give me the last day of the previous month (Code below) has not worked.</p>
  
  <p>Any ideas would be welcome. I am really stuck on this.</p>
  
  <pre><code>SELECT A.ETLKEY, A.EFFECTIVEDATE, A.ACTUAL,
  FROM DBO.View
  WHERE (A.EFFECTIVEDATE = (DateAdd("d", -1, CDate(
             "01/"+CStr(Format(&amp;EndDate(),"MM/yyyy"))))))
  </code></pre>
  
  <p>Second attempt</p>
  
  <pre><code>DECLARE @dtDate DATE
  SET @dtDate = &amp;Enddate
  --'Month/day/year'
  SELECT Case 
      When @dtDate = '08/01/2016'
      -- Last day of previous month, as this month will have no date in it
      Then DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate),0))
      -- Last day of month stated.
      Else DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
  </code></pre>
  
  <p>So final working Code for anyone who might find this useful in the futrue</p>
  
  <pre><code>DECLARE @dtDate DATETime
  SET @dtDate = &amp;Enddate
  --'Month/day/year'
  SELECT Case 
      --compares month from parameter to todays month
      When DATEPART(mm,@dtDate) = DATEPART(mm,GETDATE())
      -- Last day of previous month, as this month will have no date in it
      Then DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate),0))
      -- Last day of month stated.
      Else DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
      END
  </code></pre>