在派生列中导入字符串日期 [英] Import String Date in Derived column

问题描述

我从以下格式的文件中读取了一个字符串日期，并且出现了转换错误.

2011 年 10 月 25 日 10:18:10:756PM

我正在使用派生列将字符串转换为日期.

我在表达式中尝试了以下内容，但没有成功

(DT_DBTIMESTAMP)myDateCAST(myDate 作为日期时间)CONVERT(datetime, myDate)//我得到解析错误.

必须有一种简单的方法来进行转换.提前致谢.

解决方案

详细的答案，但我不同意是否SSIS 无法识别问题中提供的日期格式."

也许如果它被重新表述为SSIS 无法在没有帮助的情况下识别提供的日期格式."在这种情况下的根本问题是，默认情况下日期和数字解析例程是 上的一项设置，我可以使包失败.

右键单击平面文件源并选择显示高级编辑器".在输入和输出属性"选项卡上，展开输出列并找到包含日期的列.将

I have a string date being read in from a file in the following format and is giving me cast error.

Oct 25 2011 10:18:10:756PM

I am using Derived Column to cast the string to a date.

I have tried the following in the expression with no success

(DT_DBTIMESTAMP)myDate
CAST(myDate as datetime)
CONVERT(datetime, myDate)  //I get parse error on this.

there has to be an easy way to cast this. Thanks in advance.

解决方案

Detailed answer but I beg to differ as to whether "SSIS cannot recognize the date format provided in the question."

Perhaps if it was restated as "SSIS cannot recognize the supplied date format without help." The root issue in this case, is that by default the date and number parsing routines are locale aware. In general, this a good thing, except when it's not. I first stumbled over this when I was dealing with dates in a format of ccyymmdd coming off a mainframe. As others indicated, it'll parse in tsql, why not SSIS? There are plenty of articles out there advocating slicing and dicing the string data to make it into a valid datetime but why go through all that hassle?

Given this as sample input data (tab delimited).

LongDateDesiresFastParse    Gibberish
Oct 25 2011 10:18:10:756PM  Hello world
Oct 24 2010 10:18:10:756PM  Hello 2010 world
Oct 23 2009 10:18:10:756PM  Hello 2009 world
Oct 22 2008 10:18:10:756PM  Hello 2008 world

And a package that looks like this,

By changing one setting on the Flat File Source, I can make the package fail or not.

Right click on the Flat File Source and select "Show Advanced Editor." On the "Input and Output Properties" tab, expand the Output Columns and find the column containing the date. Change the FastParse setting from False to True.

When I ran it, the package originally failed as it was losing precision storing that value into a DB_TIMESTAMP. I was successful when I set the column to type DB_TIMESTAMP2

Demo package available at https://sites.google.com/site/billfellows/home/files/FastParse.dtsx?attredirects=0&d=1

这篇关于在派生列中导入字符串日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！