I believe BCP is unable to convert the format yyyyMMddHHmmssffff (which returns a value like this 202310310633130100) to a datetime . Try using this format in your export query instead:

yyyy-MM-dd HH:mm:ss.ffff

The error is because when you export the data in the file it stored into the string format and when you import the data to table the table has column with data type datetime.

To workaround this issue you need to Use a staging table (table with all datatype varchar for date time column of destination table), AFAIK You will not be able to import that date format directly into a datetime2 column with bcp. After loading data into staging table then cast the data into date time format and insert into destination.

• Create staging table to store the date into varchar format

-- staging table to store the date into varchar format
CREATE TABLE ContractDetails (
    contract_id INT,
    contractor_name VARCHAR(255),
    plan_id INT,
    quantity INT,
    exec_date VARCHAR(20),  -- Assuming the format 'yyyyMMddHHmmssffff'
    initial_plan VARCHAR(255),
    contract_status VARCHAR(50),
    backup_disksize INT,
    register_date VARCHAR(20),  -- Assuming the format 'yyyyMMddHHmmssffff'
    update_date VARCHAR(20)     -- Assuming the format 'yyyyMMddHHmmssffff'
);

• Insert the data from CSV file to this staging table.
  

• Now insert the data from staging table to destination by converting to correct format.

insert into ContractDetails2
select 
contract_id, contractor_name, plan_id, quantity, 
CONVERT(Datetime, format(cast(SUBSTRING(exec_date,1,14) as numeric), '####-##-## ##:##:##')), 
initial_plan, contract_status, backup_disksize, 
CONVERT(Datetime, format(cast(SUBSTRING(exec_date,1,14) as numeric), '####-##-## ##:##:##')), 
CONVERT(Datetime, format(cast(SUBSTRING(exec_date,1,14) as numeric), '####-##-## ##:##:##')) 
from [dbo].[ContractDetails]

OUTPUT:

Excerpt from copying date values in the documenation:
bcp uses the ODBC bulk copy API. Therefore, to import date values into SQL Server, bcp uses the ODBC date format (yyyy-mm-dd hh:mm:ss[.f...]).

Use this T-SQL FORMAT function specification, assuming you need a precision of 4 like in your question:

FORMAT(exec_date, 'yyyy-MM-dd HH:mm:ss:ffff')

I successfully imported a text file with datetime2 values in this format to Azure SQL Database with bcp .