title: System.Data.SqlClient.SqlException Thrown by Dapper When Query Result Has More Than 1000 Records
body: <p>The method call below fails with the message "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.":</p>
  
  <pre><code>public IEnumerable&lt;SomeResult&gt; GetResults(SqlConnection connection, string attribute)
      {
          var sql = string.Format(@"
          SELECT TOP 2000
              r.Id
              ,r.LastName
              ,r.FirstName
              ,r.Ssn
              ,r.CurrentId
              ,BeginDate = case when isdate(rli.BeginDate) = 1 then convert(datetime, rli.BeginDate) else NULL end
              ,EndDate = case when isdate(rli.EndDate) = 1 then convert(datetime, rli.EndDate) else NULL end
              ,rli.LcknTyCd
              ,rli.ProvId
          FROM 
              [dbo].[Span] rli
              INNER JOIN [dbo].Recipient r
                  ON rli.SysId = r.SysId
              INNER JOIN [dbo].ValidRecipient lc
                  ON r.SysId = lc.SysId
          WHERE 
              BeginDate &lt;= GETDATE()
              AND EndDate &gt;= GETDATE()
              AND rli.LcknTyCd = @LcknTyCd);
  
          return connection.Query&lt;SomeResult&gt;(sql, new { LcknTyCd = attribute}).ToList();
      }
  
  public struct SomeResult
  {
      public string Id{ get; set; }
      public string LastName { get; set; }
      public string FirstName { get; set; }
      public string Ssn { get; set; }
      public string CurrentId{ get; set; }
      public DateTime? BeginDate { get; set; }
      public DateTime? EndDate { get; set; }
      public string LcknTyCd{ get; set; }
      public string ProvId{ get; set; }
  }
  </code></pre>
  
  <p>If the result set contains 1000 (or fewer) records, the code works correctly.  When I execute the query in SQL Server Management Studio (2014 edition), I don't get an error either.  Even when I remove the TOP  from the select and execute it in SSMS, no error occurs (12,000+ records are returned, as expected).</p>
  
  <p>What should I be doing instead of the above implementation to successfully retrieve result sets with more than 1000 rows?  Would a stored procedure be more appropriate in this case?</p>