如何在 SSIS 中将 Y2K 日期转换为 SQL 日期？
我正在使用 SSIS (SQL 2008) 从 AS400 获取数据。日期值作为 7 位数字存储在 400 中。格式如下：“CYYMMDD”C 是“世纪数字”，其中 0 = 1900 且 1 = 2000。我一直在研究派生列和脚本组件。我对 SSIS 非常陌生，所有需要与不同案例混合的选角让我成为一个迟钝的男孩。另外，我正在失去前导零。我不确定这是否是 b/c 它们是数字类型，如果我是否转换为字符串，我会正确地看到它们。下面是我使用 SSIS 从 400 直接拉取后在 SQL 中看到的内容。
AS400 = Actual
101 01/01/1900 (I think these are "unknown" dates)
1231 12/31/1900 (I think these are "unknown" dates)
20702 07/02/1902
151231 12/31/1915
1000102 01/02/2000
1110201 02/01/2011
I am using SSIS (SQL 2008) to bring data over from an AS400. The date values are stored in the 400 as a 7 digit numeric. Here is the format: "CYYMMDD" C is a "century digit" where 0 = 1900 and 1 = 2000. I have been looking into derived columns and script components. I am very new to SSIS and all the casting required compounded with different cases is making me a dull boy. Also, I am losing leading zeros. I am not sure if that is b/c they are numeric type and I would see them correctly if I cast as string or not. Below is what I am seeing in SQL after a direct pull from the 400 using SSIS.
AS400 = Actual
101 01/01/1900 (I think these are "unknown" dates)
1231 12/31/1900 (I think these are "unknown" dates)
20702 07/02/1902
151231 12/31/1915
1000102 01/02/2000
1110201 02/01/2011
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
你应该能够使用这个表达式
You should be able to use this expression
首先，在派生列任务中添加前导零：
将其传递给另一个派生列任务，并使用表达式根据世纪数字执行转换，例如：
这应该给您类似 20110201 的内容。然后您可以转换它，或根据需要将其分解为日期部分。
Firstly, add the leading zero's in a derived column task:
Pass this to another Derived column task, and use an expression to perform the conversion depending on the century digit, something like:
Which should give you something like 20110201. You can then convert this, or shred it into date parts as required.
这两个答案都不是 100%，但都帮助我找出了问题所在。不确定将谁标记为“正确”这就是我所做的。必须做 2 个派生列。
Neither of the two answers were 100%, but both helped me to figure out the prob. Not sure whom to mark as "correct" Here is what I did. Had to do 2 derived columns.
如果我没记错的话，世纪标记日期并没有追溯到 1900 年，而是与任意日期有关。您可能需要查看与 Y2K 日期相关的 AS400 红皮书。我从 1992 年到 2005 年在 AS400 上进行编程。中断年份是 1939 年和 1939 年。 2039。这些之前或之后的日期分别在世纪标记系统下失败。这是因为 IBM 认为任何大于 39 的两位数年份均指 1900 年代，任何小于或等于 39 的年份均指 2000 年代。如果您正在处理未来的日期，这可能会造成麻烦。
If I recall correctly, the century mark date didn't go back to 1900, but rather had to do with an arbitrary date. You might want to check the AS400 redbooks related to the Y2K dates. I programmed on the AS400 from 1992 - 2005. The break years are 1939 & 2039. Date before or after these respectively fail under the century mark system. This is because IBM decided that any two digit year greater than 39 referred to the 1900's, anything less than or equal to 39 referred to the 2000's. If you are dealing with future dates, this might cause a snag.