How to I fix this MSS 2008 BULK INSERT datetime precision error?

Question

An MS SQL Server 2008 BULK INSERT to a datetime column introduces an error in the 3rd decimal place of the seconds portion: 2009-09-19 15:02:41.328 in the input file becomes 2009-09-19 15:02:41.327 in the database.

Here is what the input text file contains:

1   2009-09-19 15:02:41.328

Here is what the table looks like after BULK INSERT:

1> select * from mss_t
2> go
 primary_key          data_1
 -------------------- -----------------------
                    1 2009-09-19 15:02:41.327

Here is the code:

CREATE TABLE mss_t (
   primary_key    BIGINT NOT NULL PRIMARY KEY,
   data_1         DATETIME NOT NULL )
GO

BULK INSERT mss_t
FROM 'C:\temp\sa_t.txt'
GO

Answer 1

Thats not an error...

Datetime in SQL Server will always be only this

Accuracy Rounded to increments of .000, .003, or .007 seconds

Thats comming from :

http://msdn.microsoft.com/en-us/library/ms187819.aspx (Datetime Datatype information)

If you want a more precise storage than you can use the datetime2 datatype

It will have more accuracy than the plain one

http://msdn.microsoft.com/en-us/library/bb677335.aspx