How to get week of the month from a given date?

[sekar211]

I have a table with date column in it (MM/DD/YYYY).i want week of the month to be displayed on the report and i am using applysimple("datepart(wk,datepart(month,#0)",givendate) but i am not getting the correct values.
am i doing it in the right way?

Thank you,
Puneeth

 

[kpescatore]

This probably isn't the best solution but it looks like it works but I haven't tested it for very many dates. Either way it should put you in the right direction.

ApplySimple("datepart(dd,#0)/7 +1", [givendate])

 

[sekar211]

I tried your applysimple and it is working good.Thank you.
But now the user requirement has changed and they are asking for weekending date to be displayed on the report.so i am thinking of creating a new table with weekending column in it.is it the correct way to do it? or is there any other way we can do in MSTR itself with out creating a table?

Once again Thank you very much.

 

[bridgeconsultant]

My preference is to use a time table supplied by the RDBMS if it's available. Teradata example: RDBMS ships with sys_calendar. I usually query to or join to that.

select week_of_month, week_of_year from sys_calendar.CALENDAR where calendar_date = '2004-08-30'

Results:
week_of_month week_of_year
5 35