SSIS Source Format Implicit Conversion for Datetime
Solution 1:
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]
Note: DT_DATE and DT_DBTIMESTAMP has the same SET method
And i think that converting string to date is depending also on your current culture info
More detailed information are found here
- Integration Services Data Types
- SSIS TO SQL SERVER DATA TYPE TRANSLATIONS
Experiments :
After reading your comment i didn't find any related article to your question so i made the following experiments:
SSIS Implicit datetime Conversion
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
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.
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)
And Here is the Link of the Result File:
- Result.txt
SQL Server datetime Implicit Conversion
There are two datetime string formats that are interpreted correctly with with any language setting.
yyyyMMdd
yyyy-MM-ddTHH:mm:ss (ISO8601)
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()
That way you can if sqlcmd throws an exception it means that format cannot be converted.