Search code examples
ssisexpressionbuilder

How to get previous month's number

I have the following SSIS expression:

Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) + RIGHT("0" + (DT_STR,4,1252) DatePart("yyyy",getdate()),2)

which gives me 0614.

How can I change the month code so it always gives me the previous month's number?

Solution

  • Actually, I have no idea what your expression is - it looks like a mix of SQL and SSRS VBA. It may be SSIS as @mmarie suggests.

    So I'll give you two answers - SQL (which you can use in the query expression in SSRS) and the actual VBA SSRS expression.

    To adjust what you have to SQL to get the previous month, you would use:

    Right('0' + CAST(DatePart(mm, DateAdd(mm, -1, getdate())) AS VARCHAR), 2) 
+ RIGHT('0' + CAST(DatePart(yy, DateAdd(mm, -1, getdate())) AS VARCHAR), 2)

    To have this as a SSRS expression, you would use:

    =Right("0" & CStr(DatePart(DateInterval.Month, DateAdd(DateInterval.Month, -1, Today))), 2) 
& Right("0" & CStr(DatePart(DateInterval.Year, DateAdd(DateInterval.Month, -1, Today))), 2)

    To convert your original SSIS expression, you would replace getdate() with DateAdd(mm, -1, getdate()) like so:

    Right("0" + (DT_STR,4,1252) DatePart("m", DateAdd(mm, -1, getdate())), 2) 
+ RIGHT("0" + (DT_STR,4,1252) DatePart("yyyy", DateAdd(mm, -1, getdate())), 2)