在SSIS中将月份名称转换为月份编号

问题描述

我有一个输入列 MonthName，其值具有以下字符串格式 22-MAY-2017 02：29：33.00 。我想将其转换为目标表中的Datetime数据类型。

I have an input column "MonthName" which has values in the following string format 22-MAY-2017 02:29:33.00. I would like to convert this into Datetime data type in the destination table.

为此，需要完成以下转换
2017年5月22日02：29：33.00 转到 22-05-2017 02：29：33.00

For that the following conversion needs to be done22-MAY-2017 02:29:33.00 to 22-05-2017 02:29:33.00

如何在派生列任务中实现此目标。

我正在使用下面的表达式来获取值的月份名称部分，但我认为它并没有达到我的目的

How do i achieve this in Derived Column task.
I am using below expression to fetch the month name part of the value but i don't think it servers much of my purpose

SUBSTRING(MonthName,FINDSTRING(MonthName,"-",1) + 1,FINDSTRING(MonthName,"-",2) - FINDSTRING(MonthName,"-",1) - 1) <br/>

上面的表达式创建一个新列，其所有月份名称分别为：5月，6月，7月。 / p>

the above expression creates a new column with all the month names ex: may, june, july.

推荐答案

使用派生列转换

您可以使用以下表达式

Using Derived Column Transformation

You can use the following expression

LEFT([MonthName],3) +
(SUBSTRING( [MonthName],4,3) == "JAN" ? "01" :
SUBSTRING( [MonthName],4,3) == "FEB" ? "02" :
SUBSTRING( [MonthName],4,3) == "MAR" ? "03" :
SUBSTRING( [MonthName],4,3) == "APR" ? "04" :
SUBSTRING( [MonthName],4,3) == "MAY" ? "05" :
SUBSTRING( [MonthName],4,3) == "JUN" ? "06" :
SUBSTRING( [MonthName],4,3) == "JUL" ? "07" :
SUBSTRING( [MonthName],4,3) == "AUG" ? "08" :
SUBSTRING( [MonthName],4,3) == "SEP" ? "09" :
SUBSTRING( [MonthName],4,3) == "OCT" ? "10" :
SUBSTRING( [MonthName],4,3) == "NOV" ? "11" :
SUBSTRING( [MonthName],4,3) == "DEC"? "12":"")
+ RIGHT([MonthName],17)

使用S cript组件

如果Date列是字符串，则可以在脚本组件中使用 DateTime.ParseExact 方法。 （假设 outDate 是输出列，而 inDate 是输入列）

Using Script Component

If the Date column is a string you can use the DateTime.ParseExact method in a script component. (assuming that outDate is the output column and inDate is the input column)

using System;
using System.Globalization;


CultureInfo provider = CultureInfo.InvariantCulture;

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Row.outDate = DateTime.ParseExact(Row.inDate,"dd-MMM-yyyy HH:mm:ss.ff",provider).ToString("dd-MM-yyyy HH:mm:ss.ff");
}

有关此方法的更多信息，请参考以下链接：

for more info on this method you can refer to this links:

• DateTime.ParseExact Method
• C# DateTime.Parse

也可以在以下链接中查看我的答案，这非常有帮助：

Also take a look a my answer in the following link, it is very helpful:

• SSIS Source Format Implicit Conversion for Datetime

这篇关于在SSIS中将月份名称转换为月份编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！