How Does SSIS Implicitly Convert Datetime Formats, and Does DT_DBTimestamp Support '1-Jan'?

Implicit Conversion of Datetime Formats in SSIS

Question: Are there any resources available that provide a comprehensive list of the supported datetime formats for different SSIS data types? Is the DT_DBTimestamp data type capable of recognizing the '1-Jan' format?

Response:

General Information

The following table lists the default datetime formats supported by various SSIS data types when converting from strings:

Data Type	Default Format
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]

Specific Use Case:

In the provided example, the string '1-Jan' cannot be directly converted to a datetime using the Convert() function in SQL Server. However, when attempting to import this value into SSIS as DT_DBTimestamp, it is implicitly recognized as 1/1/2017.

Experiments:

To further investigate this behavior, several experiments were conducted:

• SSIS Implicit datetime Conversion: A script component was used to loop through all culture info and available datetime formats to check if they could be implicitly converted to DT_DBTIMESTAMP. The results were captured in a text file.
• SQL Server datetime Implicit Conversion: SQL commands were used to convert the same datetime strings and check for exceptions, which indicated whether the format could be converted.

Findings:

The experiments confirmed that the DT_DBTimestamp data type in SSIS has a lenient conversion mechanism that attempts to interpret various datetime formats, including those that are not explicitly supported in the default format. This behavior is not consistent with SQL Server's datetime conversion, which strictly adheres to specific supported formats.

References:

• [Integration Services Data Types](https://docs.microsoft.com/en-us/sql/integration-services/data-types/data-types-in-integration-services?view=sql-server-ver15)
• [SSIS TO SQL SERVER DATA TYPE TRANSLATIONS](https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/data-type-mappings-ssis-to-sql-server-import-export-wizard?view=sql-server-ver15)

The above is the detailed content of How Does SSIS Implicitly Convert Datetime Formats, and Does DT_DBTimestamp Support '1-Jan'?. For more information, please follow other related articles on the PHP Chinese website!