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.
!!
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:
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
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.
I think you would be better of by defining a special calendertable and add that to your model..
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.
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)