Function to get number of days in month

  • Comments posted to this topic are about the item Function to get number of days in month

  • How is this not better?

    Select day(dateadd(mm,DateDiff(mm, -1, getdate()),0) -1)

  • Thats clever.

  • My personal favourite

    SELECT

    DATEPART(dd, DATEADD(dd, DATEPART(dd, DATEADD(mm, 1, @day)) * -1, DATEADD(mm, 1, @day)))

    I suspect you would be hard pressed to notice the difference between any of the above, though the Client Statistics suggests the CASE solution is the most efficient

  • DECLARE @dtDate DATETIME

    SET @dtDate = '2011/02/28'

    SELECT @dtDate= dateadd(mm,datediff(mm,0,@dtDate),0)

    SELECT datediff(dd,@dtDate,dateadd(mm,1,@dtDate))

    Regards,
    Mitesh OSwal
    +918698619998

  • How about this?

    RETURN DAY(DATEADD(d,-1,DATEADD(mm,DATEDIFF(m,0,@day)+1,0)))

  • Here is some psuedo code for a correct leap year test calculation.

    if year modulo 400 is 0

    then is_leap_year

    else if year modulo 100 is 0

    then not_leap_year

    else if year modulo 4 is 0

    then is_leap_year

    else

    not_leap_year

    Since this, and other date checking code, is built into the TSQL Date functions I recomend using the examples that have already been posted.

  • Well, since everyone else likes to do subtractions, I thought I'd contribute an addition only solution 😀 (if you don't count DATEDIFF as subtraction :-))

    SELECT DATEDIFF(dd,@testdate,(DATEADD(m,1,@testdate)))

  • Of course, if you use a calendars table you never have to worry about having to code or validate any date calculations ever again. 😛 Sure makes life a lot simpler. Here is how I would get that answer:

    SELECT d.DaysInCalendarMonth

    FROM dbo.dwCalendars d

    WHERE d.CalendarDate = @SomeDate

  • nice one..:-)

  • Well played!

    Slope (4/27/2011)


    Of course, if you use a calendars table you never have to worry about having to code or validate any date calculations ever again. 😛 Sure makes life a lot simpler. Here is how I would get that answer:

    SELECT d.DaysInCalendarMonth

    FROM dbo.dwCalendars d

    WHERE d.CalendarDate = @SomeDate

  • Obs (4/28/2011)


    Well played!

    Slope (4/27/2011)


    Of course, if you use a calendars table you never have to worry about having to code or validate any date calculations ever again. 😛 Sure makes life a lot simpler. Here is how I would get that answer:

    SELECT d.DaysInCalendarMonth

    FROM dbo.dwCalendars d

    WHERE d.CalendarDate = @SomeDate

    Only if the Calendars table was created using a LOT differant math than the one presented for this function.

  • Back when school was cool (and storage expensive!) they taught us not to store calculated information.

    I suspect that all of the functions presented here are lower cost than a read.

  • how to give any mnth to the above query?

  • Does anyone noticed this logic:

    Month < 8 AND month <> 2 => days_in_month = 30 + (month & 1)

    Month >= 8 => days_in month = 30 + ((month+1) & 1)

    Of course, there is standard for the February

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

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