In SQL Server 2014 I tried the following query:

select * 
from MyTable 
where StartDate = '2021-12-31T00:00:00.0000000'

I get this error:

Msg 295, level 16, state 3, row 3
Conversion failed when converting character string to smalldatetime data type.

As far as I can tell this string is in ISO 8601 format, so it should be accepted by SQL Server (I read that this should actually be the preferred format when passing dates to SQL Server).

Is there a way to tell SQL Server to accept this format?

Please note: this query is actually generated by Linq in an Entity Framework Core DataContext, so I can't change the query itself in any way.

CodePudding user response：

You could rewrite your query using an unambiguous date literal on the RHS of the comparison:

SELECT * 
FROM MyTable 
WHERE CAST(StartDate AS date) = '20211231';

CodePudding user response：

• Older versions of EF Core did not support smalldatetime.
• This was fixed in version 2.2.0 in 2018.
• Update your EF Core version to something more recent than 2.2.0.
• If you are using a modern EF Core version, then you should file this as a regression bug.
• But better yet: don't use smalldatetime in your database design in the first place. Use datetime2(n) or some other appropriate type instead.
  • There is no good reason for using smalldatetime in a new database design (nor other types like text, ntext, image, timestamp, etc)
  • And you should be able to run ALTER TABLE to change the column type to datetime2(7) with no compatibility issues unless something else is really horribly designed in your system, in which case you should fix that.
  • If you really need to expose data as smalldatetime because of some horrible and opaque, but business-critical, binary that you can't rebuild then you can add a VIEW, SYNONYM and other shims to transparently convert datetime2(n) to smalldatetime while the rest of the system can live in modernity.