How Does SSIS Handle Implicit Datetime Conversions and What Formats Are Supported?

SSIS Source Format Implicit Conversion for Datetime

Introduction

When working with dates in SSIS, it's important to consider the different formats supported by the various data types. This is because SSIS may perform implicit conversions when it encounters date values in a format that does not match the expected type.

Question

One user has encountered a situation where they are trying to convert a date in the format '1-Jan' to a datetime type (DT_DBTimestamp). They expected the conversion to fail, but instead, SSIS implicitly interpreted the value as '1/1/2017'. This user is seeking a reference to show the supported date formats for different SSIS data types.

Answer

Default Formats of Datetime Datatypes in SSIS

The default formats for datetime datatypes in SSIS when converting from a string are as follows:

• 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]

Implicit Conversion Experiments

To investigate the implicit conversion behavior in SSIS, an experiment was conducted using a script component as the data source. The script iterated over various culture info and datetime formats to determine which ones were accepted by the DT_DBTIMESTAMP output column. The results were saved to a text file and revealed that many datetime formats are implicitly converted.

SQL Server Implicit Conversion

In SQL Server, only two datetime string formats are interpreted correctly regardless of language settings:

• yyyyMMdd
• yyyy-MM-ddTHH:mm:ss (ISO8601)

The above is the detailed content of How Does SSIS Handle Implicit Datetime Conversions and What Formats Are Supported?. For more information, please follow other related articles on the PHP Chinese website!