Post reply

fetch weeks duration from a date object containing months data

  • September 25, 2008 at 11:15 pm

    #96548

    Hi,

    I need to separate out the number of weeks available between a given start and end date in a SQL object.

    Something like if the start date is 01/09/2008 and end date is 30/09/2008 , then i need to separate out the number of weeks between the above dates.

    Can anybody throw some light on this please ?

    Regards,

    Vijoy

  • September 25, 2008 at 11:38 pm

    #877426

    Check the following link

    http://weblogs.sqlteam.com/jeffs/archive/2008/07/31/week-days-between-two-dates.aspx

  • September 26, 2008 at 12:08 am

    #877433

    Check whether this will solve it for you:

    select datediff(ww,'09/01/2008','09/30/2008')

  • September 26, 2008 at 1:12 am

    #877460

    Thanks for a prompt response.

    Gayathri, what 'select datediff(ww,'09/01/2008','09/30/2008')' would return is 4 every time for every month.

    I need something like in this month i.e September there are actually 5 weeks starting from 1st of september to 30th September.

    So actually the SQL should return me 5 instead of 4.

    Will look forward to your reply. 🙂

  • September 26, 2008 at 3:09 am

    #877499

    select datediff(ww,'08/01/2008','08/31/2008') +1

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • September 26, 2008 at 3:14 am

    #877501

    bhuvnesh.dogra (9/26/2008)

    select datediff(ww,'08/01/2008','08/31/2008') +1

    select datediff(ww,'08/01/2008','08/02/2008') +1 will not work as per your condition.For a single day it will give diff of 1 week....

  • September 26, 2008 at 3:19 am

    #877504

    Hope this will solve:

    declare @startdate datetime

    declare @enddate datetime

    select @startdate = '10/17/2008'

    select @enddate ='10/27/2008'

    select

    case

    when datediff(dd,@startdate,@enddate ) < 7 then

    case when DATEPART ( dw , @startdate ) > DATEPART ( dw , @enddate )

    then 1

    else 0

    end

    when datediff(dd,@startdate,@enddate ) % 7 = 0 and datediff(dd,@startdate,@enddate) / 7 >1

    then (datediff(dd,@startdate,@enddate) / 7 )+ 1

    else

    datediff(dd,@startdate,@enddate) / 7 +

    case when datediff(dd,@startdate,@enddate ) % 7 >0 then 1

    else 0

    end

    end

  • September 26, 2008 at 3:20 am

    #877507

    Asked Query is basically : return no if week in passed dates

    so i think ...my query is returning no of weeks in a month .

    for ex: select datediff(ww,'05/01/2008','05/03/2008')

    it will give : 1 which is correct ..i think so

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 8 posts - 1 through 7 (of 7 total)

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