Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2010
    Posts
    153

    Smile Unanswered: calculating number of days from the current month

    Hi guys,

    i have a problem. I want to calculate number of days of the current month. if november then i should get 30 and if december it should be 31. Based on days i want to divide it by 7 WHCIH I WILL DO BUT I JUST WANT TO KNOW HOW TO GET NUMBER OF DAYS



    Thanks..

  2. #2
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    nice querry
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    Perhaps not the most elegant...
    Code:
    declare @date varchar(10)
    set @date = '2010-11-17'
    
    select 
    case
    when 1 = isdate(convert(varchar, year(@date)) + '-' + convert(varchar, month(@date)) + '-31') then 31
    when 1 = isdate(convert(varchar, year(@date)) + '-' + convert(varchar, month(@date)) + '-30') then 30
    when 1 = isdate(convert(varchar, year(@date)) + '-' + convert(varchar, month(@date)) + '-28') then 28
    end

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Here's my version that is a bit more concise, but certainly more obtuse:

    Code:
    declare @date date
    set @date='2010-08-31'
    
    select day(dateadd(day,-1,convert(char(4),year(DATEADD(month,1,@date)))+'-'+convert(varchar(2),month(DATEADD(month,1,@date)))+'-1'))
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    any formula involving strings means conversions, and those are always a ~lot~ slower than simple date arithmetic

    Code:
    SELECT DAY( DATEADD(DAY,-1,
                DATEADD(MONTH,1,
                DATEADD(DAY,1-DAY(GetDate()),GetDate()) ) ) )
    start at the inside and work your way out --

    take the day of the month for today, and subtract 1 less than that from the current date

    that gives you the first of the current month

    then add 1 month (1st of next month)

    then subtract 1 day (last dat of current month) and take the day
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @d		DATETIME
    SET @d = '2000-02-12'
    
    SELECT DateDiff(d, t, n) FROM (SELECT DateAdd(m, DateDiff(m, 0, @d)
    , 0) AS t, DateAdd(m, 1 + DateDiff(m, 0, @d), 0) AS n) AS z
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    r937, wow, I really like that improvement--similar to my logic, but without the character conversions. I'm going to make it my standard.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  8. #8
    Join Date
    Sep 2010
    Posts
    153

    Smile thanks guys:) the above queres are working fine:)

    .

    i tried with another queery and it is also working . Thanks a lot for the solution



    select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))))

    thanks

  9. #9
    Join Date
    Nov 2010
    Posts
    1

    thanks

    Thanks! I appreciate it.

Posting Permissions

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