OPENROWSET not able to convert datetime from text file

Question

I have a flat file which is generated using SSIS. the flat file stores all the values for datetime datatyoe as below format

2001-02-14 09:46:48.123000000

when i try to use the flat file generated from SSIS to load data from flat file to sql server using "OPENROWSET". it throws error

Conversion failed for columnname(having datetime datatype)

any suggestion how to fix this?

the schema of source from which flat file being generated and the schema of destination where loading data from flat file are same. but still not able to load data.

Answer 1

Just truncate the trailing zeroes:

select cast( '2001-02-14 09:46:48.123000000' as datetime)-- error
select cast( '2001-02-14 09:46:48.123' as datetime)-- ok
select cast( '2001-02-14 09:46:48' as datetime) -- ok
select cast( left('2001-02-14 09:46:48.123000000', 23) as datetime) -- ok