Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Unanswered: No of Days in a month

    Hi,
    I just want to know, is there anything more better solution than this one to find out the no of days in a month ?
    I have done this but I am not satisfied,anybody has a smarter solution?
    Plz comment..

    My Solution :
    /* check leap year*/
    if year(getdate())%4<>0
    set @noofdays=(select case month(getdate())-1
    when 1 then 31
    when 2 then 28
    when 3 then 31
    when 4 then 30
    when 5 then 31
    when 6 then 30
    when 7 then 31
    when 8 then 31
    when 9 then 30
    when 10 then 31
    when 11 then 30
    when 12 then 31
    end )
    else
    set @noofdays=(select case month(getdate())-1
    when 1 then 31
    when 2 then 29
    when 3 then 31
    when 4 then 30
    when 5 then 31
    when 6 then 30
    when 7 then 31
    when 8 then 31
    when 9 then 30
    when 10 then 31
    when 11 then 30
    when 12 then 31
    end)
    Joydeep

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Look for mathematical algorithms rather than conditional statements. The resulting code is usually shorter, simpler, and more efficient. I think this will work, though I have not tested it:
    Code:
    declare	@DateValue Datetime
    set	@DateValue = getdate()
    
    select	DatePart(day, DateAdd(day, DateDiff(day, 0, Dateadd(month, 1, @DateValue))-1, 0))
    There is slightly different method, along with additional datetime formulas, on Brett's blog:
    http://weblogs.sqlteam.com/brettk/ar...6/02/5528.aspx
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by blindman
    Look for mathematical algorithms rather than conditional statements. The resulting code is usually shorter, simpler, and more efficient. I think this will work, though I have not tested it:
    Code:
    declare	@DateValue Datetime
    set	@DateValue = getdate()
    
    select	DatePart(day, DateAdd(day, DateDiff(day, 0, Dateadd(month, 1, @DateValue))-1, 0))
    There is slightly different method, along with additional datetime formulas, on Brett's blog:
    http://weblogs.sqlteam.com/brettk/ar...6/02/5528.aspx
    Thanks Batman.I tested the code but sorry it didn't worked.Well,I got the solution from the links that you gave me.I made some adjustment and found the thing I need.

    declare @datecol datetime,@dat int
    set @datecol=getdate()
    set @dat=day(DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @dateCol)+1, 0) ))
    print @dat
    Thanks for the links.
    Joydeep
    Last edited by rudra; 02-26-06 at 15:31.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •