Date Time String Transformation
The Date Time String transformation converts a date or time in a source column to a different format in the destination column.
The transformation is carried out in two steps:
- Each entry is converted in the source column to an OLE DB DBTYPE_TIMESTAMP data type. If the source column is a string type, a source date format string guides the conversion.
- The OLE DB DBTYPE_TIMESTAMP data type is converted to the destination column format. If the destination column is a string type, the destination date format string is used to format the resulting text.
If the source or destination column is not a string type, the corresponding format string is ignored.
The following standard date format strings are available:
- dd MMM yy
- dd MMMM yy HHmm
- dd MMMM yy HHmmss.ff
- dddd MM/dd/yy hh:mm tt
- dddd, MMMM dd,yyyy hh:mm:ss.ffff tt
- hh:mmtt
If yours is not among the standard formats, create your own date format string using any of the following tokens.
| Value | Token(s) |
|---|---|
| Year | yyyy, yy |
| Month | MMMM, MMM, MM, M |
| Day | dddd, ddd, dd, d |
| 12 hour | hh, h |
| 24 hour | HH, H |
| Minute | mm, m |
| Second | ss, s |
| Fraction | f (may be repeated) |
| AM/PM | Tt |
Additionally, you may specify:
- Long and short names for months.
- Long and short names for days of the week.
- The language in which dates appear.
Note All conversions use regional settings current on the instance of Microsoft® SQL Server™ running the package.
If the formatted result is too large to fit in a string type destination column, it is truncated.
The Date Time String transformation fails if:
- The source or destination column is not a string or date type.
- An invalid date format string is specified, for example, "mm/dd/mm."
- A source string does not match the source date format string.
To convert the format of a Date Time String transformation