More SSIS expressions

One of my first (and most popular!) posts was related to SSIS expressions, so I thought I'd post a few more examples.

First day of this month

As a date:

(DT_DATE)((DT_WSTR, 4)YEAR(GETDATE()) + "-" +RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2)+"-01")

As YYYYMMDD unicode string:

(DT_WSTR, 4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2)+"01"

First day of the previous month

As a date:

(DT_DATE)((DT_WSTR, 4)YEAR(DATEADD("mm", -1, GETDATE())) + "-" +RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("mm", -1, GETDATE())),2)+"-01")

As a YYYYMMDD unicode string:

(DT_WSTR, 4)YEAR(DATEADD("mm", -1, GETDATE())) + RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("mm", -1, GETDATE())),2)+"01"

Last day of the previous month

This uses the old programmer trick of subtracting one day from the first day of the current month.

DATEADD("dd", -1, (DT_DATE)((DT_WSTR, 4)YEAR(GETDATE()) + "-" +RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2)+"-01"))

Timestamp in the format YYYYMMDDHHMMSS (ie 20121217160035)

Using the start time of the containing package:

 (DT_WSTR, 4) Year(@[System::StartTime] )
+(Month(@[System::StartTime] )>9?(DT_WSTR, 2) Month(@[System::StartTime] ):"0"+(DT_WSTR, 2) Month(@[System::StartTime] ))
+(DT_WSTR, 2) Day(@[System::StartTime] )+SUBSTRING(REPLACE((DT_WSTR, 20)@[System::StartTime], ":", ""), 12, 8)

Using GETDATE():

Note that the SUBSTRING is slightly different:

(DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2) + RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2)+SUBSTRING(REPLACE((DT_WSTR, 29)GETDATE(), ":", ""), 12, 6)