Sort by smalldatetime field produces different results in SQL server and C# (Entity Framework)

sql server datetime comparison not working
sql server datetime best practices
how is datetime stored in sql server
sql date time error
sql server where date
sql server max datetime
linq order by ascending
t sql convert date to mm/dd/yyyy

I have a table with a field LastChange which is of type smalldatetime. When I sort by ascending order on this field, I get the following:

I have created an entity corresponding to this in Entity Framework. The type of the LastChange field is DateTime.

When I execute the same sort operation like this:

teams.OrderBy(x => x.LastChange).Select(x => x.TeamID)

I get the following result:

[0]: 158
[1]: 161
[2]: 159
[3]: 160
[4]: 165
[5]: 163
[6]: 166
[7]: 167
[8]: 168
[9]: 169
[10]: 170
[11]: 171
[12]: 1172
[13]: 162
[14]: 164
[15]: 172

Why are the 2 results different? Is it because the LastChange values are the same for multiple records? Or has this got something to do with the smalldatetime type?


This is because the LastChange field is exactly the same for several rows. Try these:

SELECT *
FROM YourTable
ORDER BY LastChange, TeamID;

teams.OrderBy(x => x.LastChange).ThenBy(x => x.TeamID).Select(x => x.TeamID)

In both places and you will obtain exactly the same results. If you don't specify the order for second field SQL will show a random result (for fields not specified), normally is the same but can be totally different.

Newest 'sql-server' Questions - Page 644, Use this tag for all SQL Server editions including Compact, Express, Azure, Fast-​track, APS (formerly PDW) Sort by smalldatetime field produces different results in SQL server and C# (Entity Framework) isnull with AND : -An expression of non-boolean type specified in a context where a condition is expected, near 'and'. When we do that, SQL Server performs an implicit conversion in order for the data to “fit” the new data type. As it turns out, we can also set the smalldatetime variable to the same string literal that includes fractional seconds (even though this data type doesn’t store fractional seconds). Here’s an example where I do just that:


Both results are correct. You expect sort order, only by "LastChange" column. Due to precision (to the minutes) of the LastChange column you are getting correct result. Sql or c# doesnt bother of other data in your select example. Eather add second order parameter or increase input precession for the LastChange column.

Solving the Datetime Mystery, Surprising information about how SQL Server handles the datetime data type formats are different from the format that SQL Server uses when converting a datetime value a date into a SQL Server datetime field or variable, confusion can result. First, your default language controls the day and month order, and for US  Find answers to Specifying SmallDateTime as data type for Entity Framework Code First from the expert community at Experts Exchange The target db is SQL Server 2012.


Is it because the LastChange values are the same for multiple records?

Yes. Neither c# nor the database will attempt to sort the results further after the LastChange is sorted, so for any values for LastChange that are the same, the rows within that set could be in any order, there will be no consistency - one day they will be ordered like this, and later if the db has reorganised the data in the table they will have a different order. C# might well order things completely differently as a side effect of how the rest of the sorting algorithm works. There is no guarantee that the db and c# will always sort the same way; they will each give up wen the sorting is as good as it can get on LastChange

If you always want data in a certain order, you have to sort it precisely. If the data itself doesn't have anything to order by that is consistent you have to write your own sorting system, or provide additional data

For example if we always wanted this data out of a db table that only contained names:

Tim
James
John

It's not alphabetical, reverse alphabetical, alphabetical-by-second-character, it's not sorted by length, and so on. There is nothing about this data that we can sort by in terms of the data itself, so we'll have to:

ORDER BY 
  CASE personname
    WHEN 'Tim' THEN 0
    WHEN 'James' THEN 1
    WHEN 'John' THEN 2
  END

Here we've literally converted our data into a sortable thing

TLDR; if you always want data sorted a certain way, you have to be per-row specific. The combination of the columns you sort by, must be unique. As soon as you sort by a set of columns that are not unique, the identical data could come out in any inconsistent, random order

Or has this got something to do with the smalldatetime type?

No, it affects all data sets being sorted where more than one row has the same value and nothing further helps the sort algorithm choose which of the identical rows should go first

How to: Sort Query Results by Using LINQ, The sort order for each field can be ascending order or descending order. In Visual Studio, open Server Explorer/Database Explorer by clicking Server To add a project that contains a LINQ to SQL file event to query the tables that are exposed as properties of your data context and sort the results. Teams. Q&A for Work. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information.


Known Issues and Considerations in LINQ to Entities, NET Framework 4.5, LINQ to Entities queries are automatically cached. This occurs when a column name in the order-by list matches a column name in running with a compatibility level of "80". var results = context. In Visual Basic, when a property is mapped to a column of SQL Server bit type with a  Therefore the code in the example (putting the Where statement inside an if statement) makes sure that you get the same results for all versions of SQL Server. Also, the .NET Framework implementation of the Contains method performs a case-sensitive comparison by default, but Entity Framework SQL Server providers perform case-insensitive


LINQ to SQL: Querying Data, NET Framework 4 covers LINQ to SQL features used to query data. Using ToList or ToArray, you can convert the results of a query into a List or an Passing IEnumerable<T> uses a different set of extension methods to resolve This kind of expression is resolved by the host language (C# in this case). Therefore, typecasting the string back to a smalldatetime also works. This shows that using the correct datatype helps you. And I must inform you that if you still think this a nuance, it isn't a nuance of MS SQL Server. Sybase SQL Server, the Oracle database, Interbase and Firebird behave in the same way.


Entity Framework Core Datetime Format, DateTime value result. Assigning values to DateTime fields Paging with Entity Framework Core. Beginner , c# , Entity Framework , Intermediate , LINQ , Linq to SQL , Sql Server. Difference between cast and as inside a select in LINQ. Format you can use string interpolation to produce much more readable code that  SQL Server data types SmallDateTime is for truncating duplicate DateTime and Milliseconds from datetime because it comes with limited resolution and is 4 bytes instead of 8 as in DateTime. Solution right click on the table in the object browser and click on ALTER TABLE for the create table statement, change SmallDateTime to DateTime and execute.