Home Forums SQL Server 2005 T-SQL (SS2K5) First Day and Last day of the previous month in ssis expression RE: First Day and Last day of the previous month in ssis expression

  • December 4, 2014 at 8:32 am

    #1762744

    That's normal, because you're still calculating the days for November. If you understand each part of the formula, you should be able to fix it yourself. To use different months, you could just use a date variable.

    "A_" --Initial prefix

    + RIGHT( "0" + (DT_STR, 2, 1252) MONTH(DATEADD( "MM", -1, @[User::Date] )), 2) --Gets number of previous month, uses RIGHT("0"+number,2) to include a zero for one digit months.

    + "01_" --Every month starts with day 1

    + RIGHT( "0" + (DT_STR, 2, 1252) MONTH(DATEADD( "MM", -1, @[User::Date])), 2) --Gets number of previous month, uses RIGHT("0"+number,2) to include a zero for one digit months.

    + (DT_STR, 2, 1252) DAY(DATEADD( "DD", DAY(@[User::Date]) * -1, @[User::Date] )) --Substracts the current day of the month to the date. This will give the last day of the previous month. To get previous months you need to alter this date as well as the previous ones.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2