1

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.":

public IEnumerable<SomeResult> 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 <= GETDATE()
            AND EndDate >= GETDATE()
            AND rli.LcknTyCd = @LcknTyCd);

        return connection.Query<SomeResult>(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; }
}

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).

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?

Scott Lawrence
  • 6,993
  • 12
  • 46
  • 64
  • 1
    What is the datatype of BeginDate and EndDate? What format data does it contain? Also without an order by there is no guarantee what 2,000 rows you will get. – Martin Smith Mar 24 '15 at 21:49
  • I seriously doubt this has *anything* to do with Dapper. That exception is completely unrelated. – mxmissile Mar 24 '15 at 21:51
  • I've updated my question to reflect the fact that I've tried variations of this query in SQL Server Management Studio that all work correctly. BeginDate and EndDate are (unfortunately for me) varchar(8). I'll try adding an ORDER BY clause to see if/how that changes my results. – Scott Lawrence Mar 25 '15 at 11:11

2 Answers2

2

Sounds like your date fields are stored in a varchar column. Ideally, you should change those to datetime fields. If that's not an option, change your WHERE clause to look like this:

WHERE 
  case when isdate(rli.BeginDate) = 1 then convert(datetime, rli.BeginDate) else NULL end <= GETDATE()
  AND case when isdate(rli.EndDate) = 1 then convert(datetime, rli.EndDate) else NULL end >= GETDATE()
  AND rli.LcknTyCd = @LcknTyCd);

The reason it succeeded on your top 1000 query is likely because the top 1000 records found all contained valid dates.

Jon Tirjan
  • 3,556
  • 2
  • 16
  • 24
  • I'll try changing the WHERE clause as you've suggested and see if that cures the error. – Scott Lawrence Mar 25 '15 at 11:15
  • thanks so much for your help. When I updated the WHERE clause to what you suggested, no more error. I'm still not sure why SSMS didn't give me an error with the previous query, but your suggestion has resolved my issue. – Scott Lawrence Mar 25 '15 at 14:44
2

That is a database server error: dapper doesn't know about varchar and doesn't take in terms of varchar - it talks about .net Strings. So: one of your dates-stored-as-varchar is broken and does not contain a valid value.

Basically: try this query in SSMS: I expect it will break there too!

Changing to a stored procedure will not change this at all. What needs to change is the broken data - and (more importantly) the bad choice of storing date/time data in a text-based column.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900