Date function - Week # in the Month????


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Date function - Week # in the Month????

  1. #1
    erikcharles Guest

    Date function - Week # in the Month????


    I am involved with a datwarehouse project and need to populate the TIME dimension
    with various date formats, i.e. day_of_year, First_day_of_month, etc....
    but I can't figure out how to get the 'week number in the month'. So for
    any given date within the time period of 1/1/95 to 12/31/01 I need to know
    what 'week in the month' that falls in.
    I've tried this "DATEPART(ww, getdate()) / DATEPART(m, getdate())" but this
    isn't even close.
    Of course Oracle has a date function that gives this precisely.

    Does anyone have a clue how to do this? It's the only one I'm hung up on.

    Please help!!!

  2. #2
    Gleb Guest

    Re: Date function - Week # in the Month????


    Hi, Eric!
    Here is some clues that may get what you want:
    For SQL Server 7.0. Try to use 'wk' interval instead of 'ww'.
    Also look this statement:
    "Select DATEDIFF (week, '2000-02-15', GETDATE()). This statement will return
    the difference between second and third argument as a number of time interval
    in weeks. Use this logic in your case.
    I hope it will help.

    Good luck.


    "erikcharles" <erikcharles@mindspring.com> wrote:
    >
    >I am involved with a datwarehouse project and need to populate the TIME

    dimension
    >with various date formats, i.e. day_of_year, First_day_of_month, etc....
    >but I can't figure out how to get the 'week number in the month'. So for
    >any given date within the time period of 1/1/95 to 12/31/01 I need to know
    >what 'week in the month' that falls in.
    >I've tried this "DATEPART(ww, getdate()) / DATEPART(m, getdate())" but this
    >isn't even close.
    >Of course Oracle has a date function that gives this precisely.
    >
    >Does anyone have a clue how to do this? It's the only one I'm hung up on.
    >
    >Please help!!!



  3. #3
    erikcharles Guest

    Re: Date function - Week # in the Month????


    Gleb,
    thanks for your help! it allowed me to created this final formula as:
    given a date what's the week_of_month_nbr =
    datediff(week,CONVERT(char, getdate() - (DAY(getdate()) - 1),101),getdate())+1

    /* this simply calcs the 1st day of the month and then returns the week #
    of that date and the data in question, then adds 1 to the difference. */

    thx,
    erik

    "Gleb" <glb1970@hotmail.com> wrote:
    >
    >Hi, Eric!
    >Here is some clues that may get what you want:
    >For SQL Server 7.0. Try to use 'wk' interval instead of 'ww'.
    >Also look this statement:
    >"Select DATEDIFF (week, '2000-02-15', GETDATE()). This statement will return
    >the difference between second and third argument as a number of time interval
    >in weeks. Use this logic in your case.
    >I hope it will help.
    >
    >Good luck.
    >
    >
    >"erikcharles" <erikcharles@mindspring.com> wrote:
    >>
    >>I am involved with a datwarehouse project and need to populate the TIME

    >dimension
    >>with various date formats, i.e. day_of_year, First_day_of_month, etc....
    >>but I can't figure out how to get the 'week number in the month'. So for
    >>any given date within the time period of 1/1/95 to 12/31/01 I need to know
    >>what 'week in the month' that falls in.
    >>I've tried this "DATEPART(ww, getdate()) / DATEPART(m, getdate())" but

    this
    >>isn't even close.
    >>Of course Oracle has a date function that gives this precisely.
    >>
    >>Does anyone have a clue how to do this? It's the only one I'm hung up on.
    >>
    >>Please help!!!

    >



  4. #4
    Massimo Petrini Guest

    Re: Date function - Week # in the Month????


    IN the same way I need to found a function to know the week # in the year,
    for examples number=WeekNumberOfYear(date())

    Can you help me ?

    "erikcharles" <erikcharles@mindspring.com> wrote:
    >
    >Gleb,
    >thanks for your help! it allowed me to created this final formula as:
    >given a date what's the week_of_month_nbr =
    >datediff(week,CONVERT(char, getdate() - (DAY(getdate()) - 1),101),getdate())+1
    >
    >/* this simply calcs the 1st day of the month and then returns the week

    #
    >of that date and the data in question, then adds 1 to the difference. */
    >
    >thx,
    >erik
    >
    >"Gleb" <glb1970@hotmail.com> wrote:
    >>
    >>Hi, Eric!
    >>Here is some clues that may get what you want:
    >>For SQL Server 7.0. Try to use 'wk' interval instead of 'ww'.
    >>Also look this statement:
    >>"Select DATEDIFF (week, '2000-02-15', GETDATE()). This statement will return
    >>the difference between second and third argument as a number of time interval
    >>in weeks. Use this logic in your case.
    >>I hope it will help.
    >>
    >>Good luck.
    >>
    >>
    >>"erikcharles" <erikcharles@mindspring.com> wrote:
    >>>
    >>>I am involved with a datwarehouse project and need to populate the TIME

    >>dimension
    >>>with various date formats, i.e. day_of_year, First_day_of_month, etc....
    >>>but I can't figure out how to get the 'week number in the month'. So for
    >>>any given date within the time period of 1/1/95 to 12/31/01 I need to

    know
    >>>what 'week in the month' that falls in.
    >>>I've tried this "DATEPART(ww, getdate()) / DATEPART(m, getdate())" but

    >this
    >>>isn't even close.
    >>>Of course Oracle has a date function that gives this precisely.
    >>>
    >>>Does anyone have a clue how to do this? It's the only one I'm hung up

    on.
    >>>
    >>>Please help!!!

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center