0

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?

Si8
  • 9,141
  • 22
  • 109
  • 221
  • 1
    Are you sure this is SSRS? I think you meant to tag SSIS rather than SSRS. This is a valid expression in Integration Services, which is where you can find Expression Builder dialogue. – mmarie Jun 19 '14 at 23:51
  • No it is SSRS Expression Builder. – Si8 Jun 20 '14 at 01:38
  • Opppps... you are right, it is SSIS package :/ – Si8 Jun 20 '14 at 13:08

1 Answers1

1

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)
Chris Latta
  • 20,316
  • 4
  • 62
  • 70
  • mmarie is right. I just had a long day :)... So those two expression is invalid in the Expression Builder for SSIS – Si8 Jun 20 '14 at 13:09