Get month days

  • Comments posted to this topic are about the item Get month days

    Igor Micev,My blog: www.igormicev.com

  • CREATE FUNCTION dbo.fnGetMonthDays(@myDate DATETIME) RETURNS INT

    AS

    BEGIN

    return DATEDIFF(d, DATEADD(month, DATEDIFF(month, 0, @mydate) , 0) ,DATEADD(d, -1, DATEADD(m, 1, DATEADD(month, DATEDIFF(month, 0, @mydate) , 0)))) + 1

    END

    go

    select dbo.fnGetMonthDays3('2/1/2000')

    -- Returns 29

    -- It's kludgy, but it works.

  • robolance (10/29/2014)


    CREATE FUNCTION dbo.fnGetMonthDays(@myDate DATETIME) RETURNS INT

    AS

    BEGIN

    return DATEDIFF(d, DATEADD(month, DATEDIFF(month, 0, @mydate) , 0) ,DATEADD(d, -1, DATEADD(m, 1, DATEADD(month, DATEDIFF(month, 0, @mydate) , 0)))) + 1

    END

    go

    select dbo.fnGetMonthDays3('2/1/2000')

    -- Returns 29

    -- It's kludgy, but it works.

    Yeah, I know this trick, it's just ok.

    Thanks for enriching with another function about the topic.

    Igor Micev,My blog: www.igormicev.com

  • I ran both versions against a DB table with about 1.2 million rows.

    Without the get days it took 35 seconds.

    With Igor's version it took 39 seconds.

    With my version it took 39 seconds.

    Not really a 'scientific' test, but a quick indicator.

    I kinda expected that they would be very similar. It is just a matter taste.

  • robolance (10/31/2014)


    I ran both versions against a DB table with about 1.2 million rows.

    Without the get days it took 35 seconds.

    With Igor's version it took 39 seconds.

    With my version it took 39 seconds.

    Not really a 'scientific' test, but a quick indicator.

    I kinda expected that they would be very similar. It is just a matter taste.

    Very good from your side. I've been using that way for a NoSQL db, and it worked well.

    Thanks!

    Igor Micev,My blog: www.igormicev.com

  • Hi,

    This is Simple Function to get Month days for current Month

    /***********************************************/

    /***********************************************/

    /***********************************************/

    CREATE FUNCTION dbo.FnDaysOfCurrentMonth(@myDate DATE) RETURNS INT

    AS

    BEGIN

    DEclare @Month int

    DECLARE @days int

    select @month=month(@myDate)

    IF @Month =2

    BEGIN

    IF (Year(@myDate)%4)=0

    BEGIN

    SET @days=29

    END

    ELSE

    BEGIN

    set @days=28

    END

    END

    ELSE

    BEGIN

    select @days =day(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@myDate)+1,0)))

    END

    RETURN @days

    END

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

  • What about this function with this date format?

    CREATE FUNCTION dbo.[fnGetMonthDays]

    (

    @DATE DATE

    )

    RETURNS INT

    AS

    BEGIN

    RETURN DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,CONVERT(DATE,@DATE,101))))

    END

    SELECT dbo.fnGetMonthDays(CONVERT(DATE,'Feb 2012'))

    --29

    rkaram

  • Thanks to All that contributed on the discussion. Everybody that will need such function will have to choose from the many versions of the function for returning days in a month.

    Igor Micev,My blog: www.igormicev.com

  • CREATE FUNCTION dbo.[fnGetMonthDays1]

    (

    @DATE DATE

    )

    RETURNS INT

    AS

    BEGIN

    RETURN DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,CONVERT(DATE,@DATE,101))))

    END

    will not work, check:

    SELECT dbo.fnGetMonthDays1(CONVERT(DATE,'31 Jan 2012'))

  • You have to use the date format like that "Feb 2012" without the 'Day'

    go try it and enjoy it 🙂

    Rabih

    rkaram

  • rabih_karam (11/10/2014)


    What about this function with this date format?

    CREATE FUNCTION dbo.[fnGetMonthDays]

    (

    @DATE DATE

    )

    RETURNS INT

    AS

    BEGIN

    RETURN DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,CONVERT(DATE,@DATE,101))))

    END

    SELECT dbo.fnGetMonthDays(CONVERT(DATE,'Feb 2012'))

    --29

    This doesn't work. Test it for '2012-2-28 5:33:21'.

  • Kindly read my previous post and the way that you should send the param @date

    the @date should be send in this format 'Feb 2012' without the DAY

    rkaram

  • My apologies. I missed that line in your post where you mentioned the difference in format. I thought you were talking about the same thing everyone else was talking about. Again, I apologize.

  • And just to mention the Microsoft supplied function for SQL version 2012, 2014 the EOMONTH.

    http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(EOMONTH_TSQL);k(SQL12.SWB.TSQLRESULTS.F1);k(SQL12.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&rd=true

Viewing 15 posts - 1 through 15 (of 19 total)

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