• 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.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)