SSIS: Expression for finding the the first and last day of the month

April 18th, 2012
by Kristof

Have you ever needed to adjust dates in SSIS to the first or last day of the month? The following expressions will find the first or last day of the month for a given date. These expressions also account for leap years.

At the end is a short example of how to simply trim the time part or reset the time part to "00:00:00.000".

Find or adjust a date to the last day of the previous month

DATEADD("D",-(DAY(GETDATE())),GETDATE())

Find or adjust a date to the first day of the current month.
"2005-01-15" will become "2005-01-01".

DATEADD("d",- (DAY(Your_date_here)) + 1,Your_date_here)

Find or adjust a date to the last day of the month.
"2005-01-15" will become "2005-01-31". Credit also goes to Aaron for correcting me.

DATEADD("D",-(DAY(DATEADD("M",1,GETDATE()))),DATEADD("M",1,GETDATE()))

If you want to trim the time, simply convert the expression to (DT_DBDATE). If you need the time back, simple convert twice. (DT_DATE)(DT_DBDATE)(expression)

Posted in SSIS | Comments (6)

6 Responses to “SSIS: Expression for finding the the first and last day of the month”

  1. Pooja Says:

    Thanks so much for this, i am relatively new to SSIS and have been struggling to get this right since couple of days ! finally worked for me !!
    Thanks a ton !

  2. Aaron Says:

    Kristof, it looks like your end-of-current-month expression has an edge-case problem:
    Input dates on the 31st day prior to a month with less than 31 days (i.e. not July nor December) yield incorrect end-of-month dates.
    For example, plugging in “10/31/2012″ will return “10/30/2012″, and “1/31/2012″ will yield “1/29/2012″.

    Sorry for the late comment; I found your article via Google search because I just ran into this problem with my own expression very much like yours.

    It might be a better approach to anchor your calculation on the first-of-the-month rather than the input date.
    My suggestion: DATEADD(“day”, -1, DATEADD(“month”, 1, ))

  3. Aaron Says:

    Sorry, comment stripped a part of my expression. Changing symbol character. Let’s try the last part again:

    My suggestion: DATEADD(“day”, -1, DATEADD(“month”, 1, [your first-of-month expression]))

  4. Kristof Says:

    Hey Aaron,

    Thanks for contributing to my blog. The expression does indeed yield the wrong date. I take the day of the current month, and subtract that from the day of the next month. The expression goes wrong when the current day is greater than the last day of the next month. I have fixed this by subtracting the day of next month. This uses a nice quirk where SSIS/SQL will automatically return the last date when you add one month to the current date, and the current day is larger than the last day of next month.

    In SQL: dateadd(m,1,’2012-10-31′) will yield 2012-11-30.

    Your expression will work too, of course. I have chosen a slightly different approach and a shorter expression since expression are hard enough to read as it is.

  5. Tiffany Says:

    Great post! Helped me a lot, however, I don’t need the time stamp at the end. I tried using the (DT_DBDATE) and it’s not working….
    (DT_DBDATE)DATEADD(“D”,-(DAY(GETDATE())),GETDATE()) returns;

    Expression cannot be evaluated.

    The expression “(DT_DBDATE)DATEADD(“D”,-(DAY(GETDATE())),GETDATE())” has a result type of “DT_DBDATE”, which cannot be converted to a supported type.

    I’m assuming I’m doing something wrong, but I’m stuck

  6. Kristof Says:

    Hi Tiffany,

    This comment somehow slipped under the radar. It looks like you are trying to assign an expression that has a result type of DT_DBDATE to a column or variable that has an entirely different data type.

    Please check the data type of the column or variable that you are assigning the expression to.

Leave a Reply

SEO Powered by Platinum SEO from Techblissonline