Search code examples
sql-serversql-server-2017

SQL Server datetime2 parse issue

When I run the following SQL at my local SQL Server instance: 

DECLARE @t DATETIME2 = '2019-12-12 00:00:00'

I get the following error:

Unable to convert @t to a System.Data.SqlClient.SqlParameter object. The specified literal cannot be converted to DateTime2(System.Data.SqlDbType), as it used an unsupported date/time format. Use one of the supported date/time formats. Literal value: 2019-12-12 00:00:00

The only way to fix it is to add 'T' between date and time 

DECLARE @t DATETIME2 = '2019-12-12T00:00:00'

At the same time, this script runs perfectly fine at my production server (without adding 'T').

I tried to find the difference and don't see anything that could affect the behavior:

  • Collation is the same at both servers - SQL_Latin1_General_CP1_CI_AS
  • Language is the same - English (United States)
  • Server version: LOCAL is Microsoft SQL Server 2017 Developer Edition (64-bit) v14.0.2027 RTM; REMOTE is Microsoft SQL Server 2017 Enterprise Edition: Core-based Licensing (64-bit) v14.0.1000 RTM
  • Host OS: LOCAL is Windows 10 Pro; REMOTE is Windows Server 2016 Datacenter

Can somebody explain, why I'm getting the error locally?

Solution

  • This is a client-side error so it seems you are running the query from SSMS with the Enable Always Encrypted (column encryption) option for the connection (connection Options-->Always Encrypted) on the remote client but not the prod server. With the option enabled, the SSMS client parses literals in order to build parameters for AE.

    Unlike '2019-12-12 00:00:00', the ISO 8601 datetime format literal '2019-12-12T00:00:00' is unambiguous and can be reliably parsed regardless of the client regional settings.

    So your options are to either use an ISO 8601 datetime format or turn of the SSMS Enable AE option for the connection.