SqlDateTime overflow on join. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

Question

I have one view (vw_users) and one table (CTUsers). My sql query used to work when I was requesting data from the view:

var result = (from i in dc.vw_Users
              where i.CTid == ctid 
              select new 
              { 
                  UserId = i.UserId,  
                  WelcomeSent = (i.WelcomeSent != null && i.WelcomeSent.ToString().Length > 0 
                                ? DateTime.Parse(i.WelcomeSent.ToString()) 
                                 : new DateTime())
              });

But now I want to fetch the WelcomeSent from the table so I use this code:

var result = (from i in dc.vw_Users 
              join k in dc.CTUsers 
              on i.CTid equals k.CTid 
              where i.CTid == ctid 
              select new 
              { 
                  UserId = i.UserId, 
                  WelcomeSent = (k.WelcomeSent != null && k.WelcomeSent.ToString().Length > 0 
                                 ? DateTime.Parse(k.WelcomeSent.ToString()) 
                                 : new DateTime()) 
              }).Distinct();

But now when I run this query I get back this error:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

The SQL view and table WelcomeSent type is DateTime and it can be NULL also. Here is an example value of WelcomeSent: 2017-03-07 00:00:00.000

Answer 1

The real issue here is the Distinct() method. If you get hold of the queries being run in SQL format and run directly on the db from SSMS they will run fine. It's the LINQ to SQL which have restriction of not allowing DateTime value less than certain value while using Distinct().

I am not sure why you need to use the weird to and fro conversion between datetime and string values.

Following are the actual queries which run on database.

First case :

SELECT [t0].[Id] AS [TokenId], [t0].[Token],     
(CASE WHEN ([t0].[ExpiryDate] IS NOT NULL) 
            AND ((CONVERT(Int,LEN(CONVERT(NVarChar(MAX),[t0].[ExpiryDate])))) > @p1) 
        THEN 1        
      WHEN NOT (([t0].[ExpiryDate] IS NOT NULL) 
            AND ((CONVERT(Int,LEN(CONVERT(NVarChar(MAX),[t0].[ExpiryDate])))) > @p1)) 
        THEN 0 
      ELSE NULL     
 END) AS [value], 
 CONVERT(NVarChar(MAX),[t0].[ExpiryDate]) AS [s] 
 FROM [dbo].[TokenView] AS [t0]WHERE [t0].[Id] = @p0

Second Case :

SELECT DISTINCT [t2].[Id] AS [TokenId], [t2].[Token], [t2].[value], [t2].[value2] AS [s], [t2].[value3]
FROM (    
    SELECT [t0].[Id], [t0].[Token], 
    (CASE WHEN ([t1].[ExpiryDate] IS NOT NULL) 
                AND ((CONVERT(Int,LEN(CONVERT(NVarChar(MAX),[t1].[ExpiryDate])))) > @p0) 
            THEN 1            
          WHEN NOT (([t1].[ExpiryDate] IS NOT NULL) 
                    AND ((CONVERT(Int,LEN(CONVERT(NVarChar(MAX),[t1].[ExpiryDate])))) > @p0))
            THEN 0            
          ELSE NULL         
    END) AS [value], 
    CONVERT(NVarChar(MAX),[t1].[ExpiryDate]) AS [value2], @p1 AS [value3]    
FROM [dbo].[TokenView] AS [t0]    
    INNER JOIN [dbo].[Tokens] AS [t1] ON [t0].[Id] = [t1].[Id]) AS [t2]
WHERE [t2].[Id] = @p2

I would suggest following solution for the second query to resolve the issue.

Create a class which can hold the data you select from the database using query.

public class MyClass
{
    public int Id { get; set; }

    public string Token { get; set; }

    //Consider this property as WelcomeSent property from your code.
    public DateTime? ExpiryDate { get; set; }

    //Use this property only to display the data in UI.
    public DateTime FormattedExpiryDate
    {
        get
        {
            return ExpiryDate != null && ExpiryDate.ToString().Length > 0
                                        ? DateTime.Parse(ExpiryDate.ToString())
                                         : new DateTime();
        }
    }
} 

Change the query as following.

var query = (from i in dc.TokenViews //TokenViews is same as vw_Users of yours.
                     join k in dc.Tokens //Tokens is same as CTUsers
                     on i.Id equals k.Id
                     where i.Id == 5 //Hardcoding the selection criteria. 
                     select new MyClass
                     {
                         Id = i.Id,
                         Token = i.Token,
                         ExpiryDate = k.ExpiryDate //Retrieving values as it is from the db.
                      }).Distinct();

Display the value as following to see the different between ExpiryDate and FormattedExpiryDate

foreach (var item in query)
{
    Console.WriteLine("Id : {0}", item.Id);
    Console.WriteLine("Token : {0}", item.Token);
    Console.WriteLine("ExpiryDate : {0}", item.ExpiryDate);
    Console.WriteLine("FormattedDate : {0}", item.FormattedExpiryDate);
}

You can use the same approach for the first query too. This will avoid all the CASE WHEN clauses and CONVERTs in the SQL query.