Date Must Be Between 1/1/1753 12:00:00 Am And 12/31/9999 11:59:59 Pm Overflow Error Sqlbulkcopy

I am reading data from an access db and storing it in a temporary sql table then truncate the main sql table and insert the fresh data set and i am accomplishing that task using th

Solution 1:

Well, MS Access represents its datetime data type as a double:

• The epoch (zero point) of the MS calendar is 30 December 1899 00:00:00
• The integer portion of the double is the offset in days from the epoch, and
• The fractional portion of the double is the fractional part of the day.

Per the specification, the domain of the date portion of an MS Access datetime is

• lower bound: 1 January 100
• upper bound: 31 December 9999

And since the domain of a SQL Server datetime is:

• lower bound: 1 January 1753
• upper bound: 31 December 9999

any dates in your MS Access database prior to 1 January 1753 are going to cause problems. You need to find the bogus data and fix it. A couple of approaches:

• In your access database, create a view/query to present the data in a form palatable to SQL Server. Then, bulk load from that into SQL Server.

• Often, since it's pretty much a foregone conclusion that your source data is dirty/corrupted, when bulk loading data into SQL Server, one will bulk load the source data into a working table where all the columns are nullable, of type varchar types and that has no constraints/keys. Once that's done, then run a stored procedure that does the necessary cleanup and massaging of the data prior to moving it to its proper home.