title: sql datefromparts not working as expected
body: <p>I have the following code:</p>
  
  <pre><code>Declare @indDate date = '1/1/2014'
  
  SELECT  RecordID AS RID,
              Name, 
              Company, 
              PhoneNumber, 
              CONVERT(VARCHAR(10),DateOfInduction,103) AS StartDate,
              CONVERT(VARCHAR(10),DateOfExpiry,103) AS DateOfExpiry
      FROM Records WHERE DateOfExpiry &lt;= DATEFROMPARTS(DATEPART(YYYY,@indDate),DATEPART(MM,@indDate),DATEPART(DD,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@indDate)+1,0))))
      AND DateOfExpiry &gt;= DATEFROMPARTS(DATEPART(YYYY,@indDate),DATEPART(MM,@indDate),1)
  </code></pre>
  
  <p>Which basically gives me all expiring records for the month of the <code>@indDate</code> parameter.</p>
  
  <p>What I am trying to do is add 1 to the month in the DATEFROMPARTS to get next month's values. Now I don't want to just change the <code>@indDate</code> to the other month because in the report the date will not be selected but only something like Expires This Month, Next Month, Previous Month, etc.</p>
  
  <p>So if I change the WHERE clause to: </p>
  
  <pre><code>WHERE DateOfExpiry &lt;= DATEFROMPARTS(DATEPART(YYYY,@indDate),DATEPART(MM,DATEADD(MM,1,@indDate)),DATEPART(DD,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@indDate)+1,0))))
      AND DateOfExpiry &gt;= DATEFROMPARTS(DATEPART(YYYY,@indDate),DATEPART(MM,DATEADD(MM,1,@indDate)),1)
  </code></pre>
  
  <p>I get error: <code>Cannot construct data type date, some of the arguments have values which are not valid.</code> and I am not sure why, it works fine If I try something like:</p>
  
  <pre><code>Declare @indDate date = '1/1/2014'
  select DATEADD(mm,1,@indDate)
  </code></pre>
  
  <p>Can anyone see the problem?</p>