Jeff Moden (2/25/2013)

---

You can also cheat for performance with a little integer math. The 22800 is the year (1900*12). The "0" in the BOM forumula is 1900-01-01. The "-1" in the EOM formula is the day before that. Because of the integer math, it's very fast. I can't remember if it was Michael Valentine Jones or Peter Larsson that I first saw with this forumula.

DECLARE @ThisYear INT,

@ThisMonth INT;

SELECT @ThisYear = 2013,

@ThisMonth = 2;

SELECT BOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth-1, 0),

EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth ,-1)

Of course, the "-1" in the BOM formula can be distributed to the other constant to make the formula a bit shorter, still.

DECLARE @ThisYear INT,

@ThisMonth INT;

SELECT @ThisYear = 2012, --Leap Year!

@ThisMonth = 2;

SELECT BOM = DATEADD(mm, @ThisYear*12-22801+@ThisMonth, 0),

EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth,-1)

Both will also work correctly for dates before 1900 without modification and Leap Years are also figured correctly.