Post reply

Get month days

  • November 13, 2014 at 5:45 am

    #1758201

    Thanks DanMcClain,

    Then the function becomes very simple. Examples:

    DECLARE @date DATETIME = '02/01/2012';

    SELECT DAY(EOMONTH(@date)) AS Result;

    SET @date = '02/01/2014';

    SELECT DAY(EOMONTH(@date)) AS Result;

    Result

    -----------

    29

    Result

    -----------

    28

    Igor Micev,My blog: www.igormicev.com

  • November 13, 2014 at 6:46 am

    #1758211

    Wow. That's pretty compact. Thanks for sharing.

  • January 5, 2015 at 7:56 am

    #1767769

    In SQL Server 2012 it's just as easy as:

    DAY(EOMONTH(@date))

  • January 5, 2015 at 8:09 am

    #1767781

    In SQL Server 2012 it's as easy as:

    DAY(EOMONTH(@date))

  • November 17, 2019 at 8:58 pm

    #3698364

    I know this post is serveral years old now but wanted to say nice post on the following... definitely the right idea.

    kaj wrote:

    Igor,

    I really think you're overcomplicating it. From my point of view it is much simpler to add one month to the date and then subtract the day number, which brings you back to the last day of the previous month. Then it's only a matter of extracting the day (which must be equal to the number of days of the month of the supplied date).

    CREATE FUNCTION dbo.fn_GetMonthDays 
    ( @InputDate DateTime ) 
    RETURNS int 
    AS 
    BEGIN 
            RETURN DAY(DATEADD(m,1,@InputDate) - DAY(DATEADD(m,1,@InputDate))) 
    END

    I find it simpel and elegant.

    If you want to go for really "short" and you don't have a version of SQL that has EOMONTH(), try the following formula...

    DAY(DATEADD(mm,DATEDIFF(mm,-1,@DATE),-1))

    As a bit of a sidebar, I probably wouldn't make a function for this.  A Scalar function would take 7 times longer to run  (until SQL Server 2019 came out).  The code is short enough to just include it in the main code.

     

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

Viewing 5 posts - 16 through 19 (of 19 total)

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