Post reply

Function to get number of days in month

  • April 21, 2011 at 7:04 pm

    #389351

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

  • April 26, 2011 at 11:21 am

    #1316049

    How is this not better?

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

  • April 26, 2011 at 11:32 am

    #1316059

    Thats clever.

  • April 27, 2011 at 1:04 am

    #1316388

    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

  • April 27, 2011 at 2:40 am

    #1316412

    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

  • April 27, 2011 at 7:14 am

    #1316536

    How about this?

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

  • April 27, 2011 at 8:16 am

    #1316615

    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.

  • April 27, 2011 at 12:52 pm

    #1316828

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

  • April 27, 2011 at 3:29 pm

    #1316936

    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

  • April 28, 2011 at 1:36 am

    #1317067

    nice one..:-)

  • April 28, 2011 at 7:16 am

    #1317234

    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

  • April 28, 2011 at 7:22 am

    #1317238

    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.

  • April 28, 2011 at 7:17 pm

    #1317642

    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.

  • May 2, 2011 at 12:34 am

    #1318291

    how to give any mnth to the above query?

  • May 2, 2011 at 3:04 pm

    #1318752

    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