vendredi 6 janvier 2012 17:12
I hope this is in the correct forum. If not, please feel free to move it or tell me where I should post it.
I have a Visual Studio 2005 VB program that uses the Office 2010 InterOp libraries to pull data from an Excel Form and insert the data into SQL Server.
Here is the problem. The Excel data cell has a value of "1/1/1900" and is formated as a Date in '*3/14/2001' Excel format. The VB program reads the value into a string. When the value is pulled into the string, the value of the string is "12/31/1899" instead of "1/1/1900"
My guess is that Excel saves the value '1/1/1900' as a full date & time of "1/1/1900 00:00:00" and somwhere in pulling the data from Excel through the interop libraries it takes the time as UTC/GMT. Since we're in Eastern time (EST) that would move the time back five hours which would put the date at 12/31/1899 19:00:00. Of course this causes a problem because we take the value and insert it into a database record as a smalldatetime (which only goes down to 1/1/1900).
My fix was just to put 1/2/1900 in the form, but this brings up and interesting point of how to deal with a data descrepencey due to conversion between UTC/GMT and the machine time zone.
I'm totally guessing that this convertion is done in the InterOp code, but is this a bug in the InterOp code or is this normal?
I'm a little worried that date fields from other Excel files that we use will have the same problem (although I've heard no complaints yet).
Thanks for listening/reading.
Toutes les réponses
vendredi 6 janvier 2012 18:18Modérateur
This forum handles requests related to Open Specifications documentation issues.
The Open Specifications can be found at: http://msdn2.microsoft.com/en-us/library/cc203350.aspx.
Your question does not appear to be related to the Open Specifications documentation set.
I suggest you try the following forum to get your question answered.
Excel for Developers
Regards, Obaid Farooqi
- Marqué comme réponse Obaid FarooqiMicrosoft Employee, Moderator vendredi 6 janvier 2012 18:18
vendredi 6 janvier 2012 18:20Thank you. I'll try there. Maybe the moderator can delete this thread as I am going to post it in the location suggested above. Thanks!
- Modifié Alan Edwards KAE vendredi 6 janvier 2012 18:59 added request for moderator to delete