How to get week form the Date column

[jd]

Hi,

I have Date dcolumn in my report using that how should I get the Week

our Date format is: Mar 6, 2007 12:00:00 AM

Week format should be: 2007/03/01

any idea how to get it.

[pooja]

!!

[blom0344]

Would depend on how your definition is. Do you define the first 7 days of each month as week1, days 8-14 as week and soforth?
then:

Code Select Expand


cast(extract(year,current_date) as varchar(4))||'/'
||
CASE WHEN extract(month,current_date) < 10 THEN '0'||cast(extract(month,current_date) as varchar(1)) ELSE
cast(extract(month,current_date) as varchar(2)) END
||'/'||
CASE
WHEN extract(day,current_date) between 1 and 7 THEN '01'
WHEN extract(day,current_date) between 8 and 14 THEN '02'
WHEN extract(day,current_date) between 15 and 21 THEN '03'
WHEN extract(day,current_date) between 22 and 28 THEN '04'
ELSE '05'
END


Substitute your date item where I used the current_date

[jd]

Thank you very much bolm.

Your logic is working absolutely fine. 

Year and Months are fine but the day level

if days between 1 and 7 I need to show like : 2007/02/01  (not 2007/02/01)
if days between 8 and 14 I need to show like : 2007/02/08 (not2007/02/02)
if days between 15 and 21 I need to show like : 2007/02/15 (not2007/02/03)
etc....

one more thing for Ex; if Mar 01 2010 starts from Monday then that week should show up like 1,2,3,4,5,6(6days only) as
2010/03/01 then

7,8,9,10,11,12,13 as 2010/03/07 etc....

Please can you help me in that..

I really Appreciate your help.

[blom0344]

I think you would be better of by defining a special calendertable and add that to your model..

[tiga123]

A separate calendar table is indeed a good option.

if you want this only for this report then you can try the following:

- add a calc _week_of_year(date)
- add a calculation on the previous one, minimum(date for _week_of_year(date))

keep in mind the year; you can concat the year to the first calculation.

[tiga123]

Also keep in mind that the _week_of_year functionality uses the ISO8601 standard. (day 1 is always Monday, week 1 begins each year when the first week contains 4 days in the new year)