Formatting Dates in SSIS

When you want to format a date in a SSIS package it is not straightforward. However there are two ways you can do this.

First method is formatting the date from your TSQL. You can use the CONVERT() function to do this;

CONVERT(VARCHAR(20),GETDATE(),101) -- 08/25/2011
CONVERT(VARCHAR(20),GETDATE(),103) -- 25/08/2011
CONVERT(VARCHAR(20),GETDATE(),105) -- 25-08-2011
CONVERT(VARCHAR(20),GETDATE(),112) -- 20110825

For more formatting codes with CONVERT() function please refer this http://www.sql-server-helper.com/tips/date-formats.aspx

The second method is converting it in a expression in SSIS. Below is a sample code for that;

(DT_WSTR,4)YEAR(GETDATE()) + "/" + RIGHT(“00″ + (DT_WSTR,2)MONTH(getdate()),2) +  "/" + RIGHT(“00″ + (DT_WSTR,2)DAY(getdate()),2)


This will format the date to the format of  YYYY/MM/DD. You can switch the statements and format as you desires.


Please leave a comment if you have any questions regarding this.