SSIS 包插入“12/31/1899"而不是提供的“1/1/1900" [英] SSIS package inserting '12/31/1899' instead of '1/1/1900' that was provided

问题描述

SSIS 包插入了12/31/1899"而不是 Excel 表中提供的1/1/1900"...为什么要更改为另一个日期?这会导致下游表中出现 smalldatetime 问题.

SSIS package inserting '12/31/1899' instead of '1/1/1900' that was provided in the excel sheet...why is it changing to another date? This is causing smalldatetime issues in downstream table.

推荐答案

那是因为Lotus 1-2-3，这是侏罗纪时期流行的电子表格程序.Lotus 被错误地编码，1900 年是闰年.当然，这不是因为那是整整一个世纪，而是这个小错误已经被埋没了.

That would be because of Lotus 1-2-3, which was the prevalent spreadsheet program in the Jurassic Period. Lotus was incorrectly coded with the year 1900 being a leap year. It was not, of course, because it was a full century, but that little error was baked in.

当微软推出 Excel 时，他们想从 Lotus 窃取市场份额，所以他们故意将同样的错误构建到 Excel 中，以便人们可以将旧的 1-2-3 电子表格移植到全新的 Microsoft 产品上.

When Microsoft introduced Excel, they wanted to steal marketshare from Lotus, so they built that same error into Excel on purpose, so that folks could port their old 1-2-3 spreadsheets over to the spanky new Microsoft product.

所以 Excel 在内部表示为 0 的日期实际上不是 1900 年 1 月 1 日.它比 1 晚，以说明不存在的 1900 年 2 月 29 日，并且在导入之后shift 在 SQL 表中显示为 1899 年 12 月 31 日.

So the date that Excel represents internally as 0 is not, actually, January 1, 1900. It's off by one, to account for that non-existent February 29, 1900, and after import that shift shows up in your SQL table as December 31, 1899.

我最喜欢的部分是有人故意这样做.

My favorite part of all of this is that somebody did it on purpose.

幸运的是，您可以通过选择 在 Advanced 选项中使用 1904 数据系统 选项.不过，链接中有更多详细信息.

Luckily, you can change the Excel workbook to encode the dates as you're expecting them by selecting the Use 1904 data system option in the Advanced options. Much more detail on that in the link, though.

在您的情况下，您必须决定在 SSIS 中修复电子表格或围绕它编码是否不那么麻烦.

You'll have to decide whether fixing the spreadsheet or coding around it in SSIS is less of a bother in your situation.

这篇关于SSIS 包插入“12/31/1899"而不是提供的“1/1/1900"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！