Visitors to the SSIS forum often ask how to construct a date string in the form YYYYMMDDHHMISS (e.g. "20070207123456" for "7th February 2007, 12:34:56") using a SSIS expression. I always give the same answer so I thought I'd post the answer up here so I don't have to keep typing it. Here's the expression that I always use:
(DT_STR, 4, 1252)DATEPART("yyyy", @[System::ContainerStartTime]) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", @[System::ContainerStartTime]), 2)
You can of course substitute @[System::ContainerStartTime] for @[System::StartTime], GETDATE() or any column that is of type DT_DBTIMESTAMP, DT_DATE or DT_DBDATE.
-Jamie