Handling Datetime with decimal '2010-02-14 20:18:58.313000000'

Question

In SQL Server I have some textual data in varchar fields I am trying to convert to datetime's. The funny thing is this data at some point was in a datetime field, exported to flat file, and now I am reimporting it.

The problem is it is in this format 2010-02-14 20:18:58.313000000 and the conversion to datetime fails. I have no idea how it ended up like this when it was originally extracted from a datetime column. Basically a table was exported to a flat file by someone else. The original table was lost. I am reimporting back from the flatfile.

I could just drop the decimal but this would be like throwing out some of the data. I'd like to maintain as much precision as possible.

How can I import this data from the varchar column back into a datetime column and preserve as much accuracy as possible?

Answer 1

Nevermind, it casts with no problems once I throw out the trailing zeros past the first 3 decimals:

select cast( left('2010-02-14 20:18:58.313000000',23) as datetime)