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

[erikcharles]

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

[Gleb]

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

[erikcharles]

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

[Massimo Petrini]

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