Post reply

First Day and Last day of the previous month in ssis expression

  • December 3, 2014 at 1:52 pm

    #147184

    How to find the firstday and last day of previous month in ssis expression?

    Example: need file name in this format A_1101_1130.xls

    Thanks in advance.

  • December 3, 2014 at 2:43 pm

    #1762517

    I believe that this should do it. You should test it adequately.

    "A_"

    + RIGHT( "0" + (DT_STR, 2, 1252) MONTH(DATEADD("MM", -1, GETDATE())), 2)

    + "01_"

    + RIGHT( "0" + (DT_STR, 2, 1252) MONTH(DATEADD("MM", -1, GETDATE())), 2)

    + (DT_STR, 2, 1252) DAY(DATEADD( "DD", DAY(GETDATE()) * -1, GETDATE() ))

    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

  • December 3, 2014 at 2:58 pm

    #1762519

    Thanks. But this doesn;t work for month of October.

  • December 3, 2014 at 3:09 pm

    #1762522

    What do you mean by "doesn't work"? Error? Incorrect results? Detonates atomic bomb?

    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

  • December 4, 2014 at 7:10 am

    #1762701

    For October I used:

    "A_"

    + RIGHT( "0" + (DT_STR, 2, 1252) MONTH(DATEADD("MM", -2, GETDATE())), 2)

    + "01_"

    + RIGHT( "0" + (DT_STR, 2, 1252) MONTH(DATEADD("MM", -2, GETDATE())), 2)

    + (DT_STR, 2, 1252) DAY(DATEADD( "DD", DAY(GETDATE()) * -1, GETDATE() ))

    This gives A_1001_1030 but October had last date till 31

  • 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply