日期时间的 SSIS 源格式隐式转换 [英] SSIS Source Format Implicit Conversion for Datetime
问题描述
有谁知道显示 SSIS 中不同数据类型支持哪些日期格式的图表?例如，DT_DBTimestamp 是否支持 1-Jan 格式.我试图寻找但找不到任何东西.
Does anyone know of a chart that shows which formats of dates are supported by the different datatypes in SSIS? For example, does DT_DBTimestamp support the format 1-Jan. I tried looking but couldn't find anything.
我问的原因是我发现当我尝试执行 Convert(datetime, '1-Jan') 时，它失败了我的预期.但是，当我将这个相同的值拉入 SSIS 到 DT_DBTimestamp 中时，它会将其隐式标识为 1/1/2017 而不是将行重定向为数据类型转换错误.
The reason I ask is that I found that when I try to do a Convert(datetime, '1-Jan') it fails which I expected. However, when I pull this same value into SSIS into the DT_DBTimestamp, it will implicitly identify it as 1/1/2017 rather than redirect the row as a datatype conversion error.
推荐答案
一般信息
这些是日期时间数据类型的默认格式(从字符串转换时)
General Info
These are the default formats of datetimes datatypes (when converting from string)
DT_DBDATE
yyyy-mm-dd
DT_FILETIME
yyyy-mm-dd hh:mm:ss:fff
DT_DBTIME
hh:mm:ss
DT_DBTIME2
hh:mm:ss[.fffffff]
DT_DBTIMESTAMP
yyyy-mm-dd hh:mm:ss[.fff]
DT_DBTIMESTAMP2
yyyy-mm-dd hh:mm:ss[.fffffff]
DT_DBTIMESTAMPOFFSET
yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm]
注意:DT_DATE 和 DT_DBTIMESTAMP 具有相同的 SET 方法
而且我认为将字符串转换为日期还取决于您当前的文化信息
在这里可以找到更多详细信息
More detailed information are found here
阅读您的评论后，我没有找到任何与您的问题相关的文章，因此我进行了以下实验:
After reading your comment i didn't find any related article to your question so i made the following experiments:
SSIS 隐式日期时间转换
我创建了一个带有 Dataflowtask 的 SSIS 包.在这个数据流任务中，我创建了一个脚本组件(作为源)和一个平面文件目标.该脚本有一个类型为 DT_DbTimeStamp 的输出列 OutDate 在脚本中我使用了以下代码:
i created a SSIS package with a Dataflowtask. in this dataflowtask i created a Script Component (as a Source) and a Flat File Destination. The script has one output column OutDate of type DT_DbTimeStamp Inside the script i used the following code:
Private dtDate As Date = #01/01/2016#
Public Overrides Sub CreateNewOutputRows()
Output0Buffer.AddRow()
Using sw As New IO.StreamWriter("D:Result.txt", False)
sw.WriteLine("CultureInfo;Date;Format;Accepted")
sw.Close()
End Using
For Each ci As System.Globalization.CultureInfo In System.Globalization.CultureInfo.GetCultures(Globalization.CultureTypes.AllCultures)
For Each strFormat As String In ci.DateTimeFormat.GetAllDateTimePatterns
Dim boolResult As Boolean = True
Try
Output0Buffer.OutDate = dtDate.ToString(strFormat)
boolResult = True
Catch ex As Exception
boolResult = False
End Try
Using sw As New IO.StreamWriter("D:Result.txt", True)
sw.WriteLine(ci.Name & ";" & dtDate.ToString(strFormat) & ";" & strFormat & ";" & boolResult.ToString)
sw.Close()
End Using
Next
Next
End Sub
首先，我遍历所有文化信息，并获取与其相关的所有日期时间格式并遍历它们.然后我试图将声明的日期 dtDate 转换为格式化字符串并将其分配给输出列.
First i am looping over all culture info and i am Getting all datetime formats related to it and looping over them. Then i am trying to convert the date dtDate declared to a formatted string and assign it to the Output column.
因此，如果接受将具有指定格式的字符串值分配给 DT_DBTIMESTAMP 输出列，则表示格式已隐式转换
So if assigning string value with specified format to DT_DBTIMESTAMP output column is accepted that means the format is implicit converted
Output0Buffer.OutDate = dtDate.ToString(strFormat)
这是结果文件的链接:
SQL Server 日期时间隐式转换
有两种日期时间字符串格式可以使用任何语言设置正确解释.
There are two datetime string formats that are interpreted correctly with with any language setting.
yyyyMMdd
yyyy-MM-ddTHH:mm:ss (ISO8601)
另外，你可以重复同样的实验，但这次通过创建一个 SqlCommand 并执行它:
Also, you can repeat the same experiment But this time by creating an SqlCommand and executing it:
Dim sqlcmd as new SqlCommand("SELECT CONVERT(DATETIME,'" + dtdate.ToString(strFormat) + '")"
sqlCmd.ExecuteReader()
这样，如果 sqlcmd 抛出异常，则表示无法转换格式.
That way you can if sqlcmd throws an exception it means that format cannot be converted.
这篇关于日期时间的 SSIS 源格式隐式转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
