如何在SSIS中将Y2K日期转换为SQL DATE? [英] How Do I Convert a Y2K Date to SQL DATE In SSIS?

问题描述

我正在使用SSIS(SQL 2008)从AS400带来数据.日期值以7位数字的形式存储在400中.这是格式:"CYYMMDD" C是一个世纪数字"，其中0 = 1900和1 =2000.我一直在研究派生的列和脚本组件.我对SSIS还是很陌生，所有需要配合不同案例进行的转换都使我成为一个沉闷的男孩.另外，我正在失去前导零.我不确定这是否为b/c，它们是否为数字类型，如果我强制转换为字符串，是否可以正确看到它们.使用SSIS从400直接拉出后，下面是我在SQL中看到的内容.

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

推荐答案

两个答案都不是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.

1. ((DT_WSTR,8)(<<AS400>> + 19000000)) 
2. (DT_DBDATE)(SUBSTRING(DCDateString,1,4) + "-" + SUBSTRING(DCDateString,5,2) + "-" + SUBSTRING(DCDateString,7,2))

这篇关于如何在SSIS中将Y2K日期转换为SQL DATE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！