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:

1. 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.
   
   
2. 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

Enterprise Manager

See Also

DataPumpTransformDateTimeString Object

Date Time String Transformation Properties

DTS Transformations