Convert Excel Date to Sql Server Date Time format using ACE Provider VB.Net

I am attempting to import data in a batch from an Excel Worksheet to 开发者_开发百科a Sql Server database. Everything works except for the one date field in the spreadsheet. The date returned is off by four years from the value in the spreadsheet.

Example: The Excel sheet has a date 10/24/2010 14:18, but when I look at the column in my query, the date is 10/23/2006 2:18. This pattern, 4 years and 1 day earlier, is repeated for every row in the worksheet.

The Excel column comes to me as a custom type, formatted m/d/yyyy h:mm. I receive this from an outside vendor and having them change the column is not going to be my simplest solution. I'm hoping that someone has worked with this and can point me in the right direction.

For what it's worth, the relevent part of the query is:

Select [Date Created] From MyWorksheet

TIA.

I would guess that the spreadsheet is generated from a Macintosh, or some other computer which uses the 1904 date system by default. See here for more info.

As to the solution, you could try switching the date system for the workbook before doing the import. I've never had to deal with this, so can't be sure it will work, but here's the VBA command to do that:

ActiveWorkbook.Date1904 = True

Or, as stated in the article linked above, you can just add 1,462 to each date.

EDIT: I realized that changing the date system, as I suggested above, will just move the problem up in your process, so that the error is apparent in Excel. I think adding 1,462 (or 4 years and 1 day) to each date is the only solution.

If it's that consistent, you can just do this:

Select DateAdd(dd,1, DateAdd(yy,4,[Date Created])) From MyWorksheet

Of course, you have to be sure this happens for every row in the column and it would be preferable to find the cause of the issue and fix that. I'm also not sure exactly at what level the dataadd function will run here. It sql server does it after pulling the data, you will be fine. But if sql server passes this to excel as an ole query then the dateadd function calls need to be tweaked a bit. HTH