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)

### Leave a Reply

September 25th, 2012 at 08:46

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 !

November 8th, 2012 at 00:03

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, ))

November 8th, 2012 at 00:06

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]))

November 8th, 2012 at 01:46

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.

June 25th, 2013 at 06:34

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

August 26th, 2013 at 03:34

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.