如何使用派生列转换将字符串 (YYMMDD) 转换为日期时间?

本文介绍了如何使用派生列转换将字符串 (YYMMDD) 转换为日期时间?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个输入文本文件，它由几列组成，即TransactionID、receiveddt、description 等.recieveddt 列具有以下格式的日期值 120419 (yymmdd).我想将 .txt 输入文件加载到数据库中，但目标列 recieveddt 的数据类型为 DateTime.我使用派生列转换将传入的 receiveddt 列值转换为日期时间，如下所示

I have an input text file, which consists of few columns namely TransactionID, receiveddt, description etc. The recieveddt column has date value in the following format 120419 (yymmdd). I would like to load the .txt input file into a database but the destination column recieveddt is of data type DateTime. I used the derived column transformation to convert the incoming receiveddt column value to datetime as shown below

Derived Column Name Derived Column Expression Data Type ------------------- ----------------- -------------------- --------- receiveddate replace reciveddt (DT_DATE)[reciveddt] datetime

我尝试了不同的组合，但在尝试执行类型转换时总是出现错误.

I tried different combinations but I keep gettings errors while attempting to perform a type cast.

如何使用派生列转换将字符串 (YYMMDD) 转换为日期时间?

How can I convert string (YYMMDD) to datetime using derived column transformation?

推荐答案

表达式:

(DT_DATE)("20" + SUBSTRING([ReceivedDt], 1, 2) + "-" + SUBSTRING([ReceivedDt], 3, 2) + "-" + SUBSTRING([ReceivedDt], 5, 2))

为了可读性:

(DT_DATE)("20" + SUBSTRING([ReceivedDt], 1, 2) + "-" + SUBSTRING([ReceivedDt], 3, 2) + "-" + SUBSTRING([ReceivedDt], 5, 2))

问题原因:

您不能将 YYMMDD 格式的字符串转换为有效的日期值.使用上述表达式在值前加上 20 将值转换为可以转换为的格式 YYYYMMDD日期.

使用OLE DB 源、派生列转换和两个多播转换来配置数据流任务.

Configure the Data Flow Task with a OLE DB Source, Derived Column Transformation and two Multicast Transformations.

使用以下具有 YYMMDD 格式的值的查询配置 OLE DB 源.

Configure the OLE DB Source with the following query that has the values in format YYMMDD.

SELECT '120304' AS ReceivedDt UNION SELECT '120107' AS ReceivedDt UNION SELECT '121211' AS ReceivedDt UNION SELECT '121312' AS ReceivedDt;

配置派生列转换，将ReceivedDt列中的传入值以YYMMDD格式转换为ReceivedDate 格式 YYYYMMDD.

Configure the Derived Column Transformation to convert the incoming value in column ReceivedDt in format YYMMDD to ReceivedDate of format YYYYMMDD.

在派生列转换和多播转换之间附加数据查看器.将错误输出配置为 Redirect row 而不是 Fail 组件.

Attach data viewer between the Derived Column Transformation and the Multicast Transformations. Configure the Error Output to Redirect row instead of Fail component.

执行包将显示可以在派生列转换和成功多播转换之间的数据查看器中转换为有效日期值的字符串值.

Executing the package will display the string values that could be converted to valid date values in the data viewer between Derived Column Transformation and the success Multicast Transformation.

执行包将在派生列转换和错误多播转换之间显示无法在数据查看器中转换为的字符串值.

Executing the package will display the string values that could not be converted to in the data viewer between Derived Column Transformation and the error Multicast Transformation.