SQL Query failing with error "The fractional part of the provided time value" via C# App

Question

Hello,

Please help out a fellow developer. I am running a C# App and trying to run a query, but it is failing with the following error:
"The fractional part of the provided time value overflows the scale of the corresponding SQL Server parameter or column. Increase bScale in DBPARAMBINDINFO or column scale to correct this error."

The column in question is of type 'datetime2'. This was working with 'SQLOLEDB.1' but failing with 'MSOLEDBSQL19.1'.

I am using the following code for running query:

//connection string  
string serverIP = "xxxxxxxxxxxxxxxxxx";  
string dbname = "xxxxxxxxxxxxxxxxxx";  
string userid = "xxxxxxxxxxxxxxxxxxx";  
string password =   "xxxxxxxxxxxxxxxxx";  
string str2 = string.Format("Provider=MSOLEDBSQL19.1;Password={0};Persist Security Info=True;User ID={1};Initial Catalog={2};Data Source={3};Initial File Name={4};Use Encryption for Data=Optional;Trust Server Certificate=False;Server SPN={5};Authentication={6};Access Token={7};Host Name In Certificate={8};", password, userid, dbname, serverIP, "\"\"", "\"\"", "\"\"", "\"\"", "\"\"");  

//query part  
string sql = @"INSERT INTO TEST_TABLE (COL1, COL2,Date) VALUES (?, ?, ?)";  
OleDbConnection con = null;  
con = new OleDbConnection(str2);  
con.Open();  
tran = con.BeginTransaction();  

DBHelper.ExecuteNonQuery(tran, CommandType.Text, sql,  
  new OleDbParameter("?", "01234"),  
  new OleDbParameter("?", "56789"),  
  new OleDbParameter("?", DateTime.Now)  

);  

Table Schema:
CREATE TABLE [dbo].[TEST_TABLE ](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[COL1] varchar NOT NULL,
[COL2] varchar NOT NULL,
[Date] datetime2 NULL)

I have already tried adding the parameter "DateTypeCompatibility=80;DateTimeFormat=\"dd-mm-yyyy hh:mm:ss.fffffff\"" to connection string.
I have also tried SQL Native Client Driver 10/11 as well.
Edit:
Tried: datetime2 type instead of datetime2(0)
Tried: datetime2(7) type instead of datetime2(0)

What could be a solution to this? My requirement is not to change the code, but to make change in either DB or connection string.
I tried a code change -> new OleDbParameter("?", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.fffffff")) which does work but code change
may not be possible for some reason.

Answer 1

you need to set the scale of the datetime2. try datetime2(7)

Answer 2

I started a project (works but needs more narrative) on datetime2(7) precision with values like 2022-11-26 17:44:28.4006356 with zero issues. To try it online outside of code run this example. I wrote it in SSMS version 18x.

For a code sample that uses both a data provider and in another example EF Core to match up results see the following GitHub repository.

Why explore the above? Perhaps to see differences between your code and what I came up with.