使用VB脚本将varchar转换为datetime [英] Convert varchar to datetime using VB script

问题描述

我需要使用.Net将一个日期字段转换为日期时间，日期字段为varchar格式（DDMMYYYY）。我已经尝试了一些解决方案，但都是徒劳的。我的该字段的来源是一个平面文件，我的目的地是一个表。我正在使用SSIS包中的脚本。

以下是示例：

SampleDate列在 ddmmyyyy 格式。我将其转换为 MM / dd / YYYY ，以便将其转换为日期时间格式。

  Dim retString1 As String 
 Dim slash As Char = ChrW（& H2F）
 retString1 = Row.SampleDate.Substring（2，2）& /
 Dim retString2 As String 
 retString2 = Row.SampleDate.Substring（0，2）& /
 Dim retString3 As String 
 retString3 = Row.SampleDate.Substring（4，4）
 Dim retString4 As String 
 retString4 = retString1& retString2& retString3 
 
 Dim fromDt As Date = Convert.ToDateTime（retString4）
 fromDt = Row.SampleDate 
  

PS ：我不是在查询SQL查询的转换，所以在评论之前，请仔细阅读整个问题。我正在寻找一个VB代码，而不是一个SQL代码。

解决方案

只需翻转 fromdt = Row。 SampleDate

至

 行。 SampleDate = fromdt 
  

您的脚本应该可以正常工作，但如果需要，请参见下面的简单说明。 >

如何做到没有一个脚本 - 下面的脚本方法

你可以让自己更简单如果您使用派生列转换！数据转换转换不起作用，因为它不允许您操纵列的值，而只能使用数据类型。但是在派生列中，您可以剪切字符串并将结果转换为日期。

所以在数据流中将源派生到目标列。

在派生列中，添加一个新列，并使用遵循表达式（将 DateString 替换为列的名称，其中包含 ddMMyyyy中的日期格式）

然后使用它作为表达式：

 （DT_DATE）（SUBSTRING（DateString，3,2）+/+（LEFT（DateString，2）+/+ RIGHT ，4）））
  

（dt_date）可以切换为另一种日期格式，如果您愿意，该类型转换将自动设置数据类型在派生列。然后在目的地将新派生列映射到目标列而不是您的源代码中的原始列。

• 转到输入列部分，然后选择 SampleDate 列

• 返回到脚本部分，并选择Visual Basic作为脚本语言。


• 单击编辑脚本开始编码。


• 向下滚动，直到找到子 Input0_ProcessInputRow（ByVal Row ...

此代码适用于：

 公共覆盖Sub Input0_ProcessInputRow（ByVal Row As Input0B uffer）
 
如果不是Row.SampleDate_IsNull然后
 
 Row.DerivedDate = DateTime.ParseExact（Row.SampleDate，ddMMyyyy，System.Globalization.CultureInfo.InvariantCulture）
 
如果
 End Sub 
  

由于@Shiva在评论中建议你可以扩展它，并使用TryParseExact或将其包装在一个try catch块中，以便如果解析日期失败，它将简单地清除/删除记录中的值，导入将继续。因为处理无效数据，我将其留给您，这更是一个业务需求的问题。

I need to convert a date field which is in varchar format (DDMMYYYY) to DateTime using .Net. I have tried a number of solutions but all in vain. My source for that field is a flat file and my destination is a table. I am using the script in SSIS package.

Below is the sample:

The column SampleDate is in the ddmmyyyy format. I am converting it to MM/dd/YYYY in order to convert it to datetime format.

Dim retString1 As String
Dim slash As Char = ChrW(&H2F)
retString1 = Row.SampleDate.Substring(2, 2) & "/"
Dim retString2 As String
retString2 = Row.SampleDate.Substring(0, 2) & "/"
Dim retString3 As String
retString3 = Row.SampleDate.Substring(4, 4)
Dim retString4 As String
retString4 = retString1 & retString2 & retString3

Dim fromDt As Date = Convert.ToDateTime(retString4)
fromDt = Row.SampleDate

P.S. : I am not looking for SQL Query for the conversion so before commenting, please read the whole question thoroughly. I am looking for a VB code and not a SQL code.

解决方案

Just flip fromdt = Row.SampleDate

to

Row.SampleDate = fromdt

And your script should work but see below to simplify some if you want.

HOW TO DO IT WITHOUT A SCRIPT --see below for script method

You can make this a lot simpler for yourself if you use a Derived Column transformation instead! The data conversion transformation doesn't work because it doesn't allow you to manipulate the value of the column, but rather only the data type. But in the derived column you can cut up the string and cast the result as a date.

So Source to Derived Column to Destination in your data flow.

In the derived column your add a new column and use the following as the expression (replacing DateString with the name of your column containing the date in ddMMyyyy format)

Then use this as the Expression:

(DT_DATE)(SUBSTRING(DateString,3,2) + "/" + (LEFT(DateString,2) + "/" + RIGHT(DateString,4)))

The (dt_date) can be switched for another date format if you wish and that type cast will automatically set the Data Type on the Derived Column. Then in your destination map the new derived column to the destination column instead of your original column from your source.

That's it your done, no scripts.

HOW TO DO IT VIA A SCRIPT

To address the route if you wanted to stay with a script and perhaps inject some additional logic rather than simple conversion you can do it through a transformation script component in a your data flow task. It will take place of the derived column transformation above but essentially will do the same thing only through a script. I am putting steps in here that I am guessing you already know some of in case someone else stumbles on the post.

• Add the script component when you do choose "transformation" and connect it with you source
• Open Script component and go to the "Inputs and Outputs" section and add an output to hold the new column and set the datatype. A new column is necessary because you are changing data types

• go to "Input Columns" section and choose SampleDate column

• go back to "Script" section and choose Visual Basic as your script language.
• Click Edit Script to begin your coding.
• Scroll down till you find the sub Input0_ProcessInputRow(ByVal Row... this is where you will put your code.

This code works:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    If Not Row.SampleDate_IsNull Then

        Row.DerivedDate = DateTime.ParseExact(Row.SampleDate, "ddMMyyyy", System.Globalization.CultureInfo.InvariantCulture)

    End If
End Sub

As @Shiva in comments suggested you could expand this and use TryParseExact or wrap it in a try catch block so that if parsing the date fails it would simply clear/remove the value from the record and the import will continue. I leave that up to you as handling invalid data is more a question of business requirements.

这篇关于使用VB脚本将varchar转换为datetime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！