在 SSIS 中将月份名称转换为月份编号 [英] Convert month name to month number in SSIS
问题描述
我有一个输入列MonthName"，其值采用以下字符串格式 22-MAY-2017 02:29:33.00.我想将其转换为目标表中的日期时间数据类型.
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.
为此需要进行以下转换22-MAY-2017 02:29:33.00 到 22-05-2017 02:29:33.00
For that the following conversion needs to be done
22-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/>
上面的表达式创建了一个包含所有月份名称的新列，例如:may、june、july.
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)
使用脚本组件
如果日期列是字符串，您可以在脚本组件中使用 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:
还可以看看以下链接中我的回答，它非常有帮助:
Also take a look a my answer in the following link, it is very helpful:
这篇关于在 SSIS 中将月份名称转换为月份编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
