function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Maros SitkoMaros Sitko 

Bug in WEEK_IN_YEAR ?

Hi,

I do not know if it's bug, or I do not clearly understand date function : WEEK_IN_YEAR. Can someone explain it to me?

 

I have Aggragate soql query for sum col1 and col 2 in this and last week. It must be sort by week in year, because if last week is in previous month I will get bad order.

select WEEK_IN_MONTH(date1), sum(col1), sum(col2), CALENDAR_MONTH(date1),  WEEK_IN_YEAR(date1) from Obj1 WHERE (date1 = THIS_WEEK OR date1 = LAST_WEEK) GROUP BY WEEK_IN_MONTH(date1), WEEK_IN_YEAR(date1),CALENDAR_MONTH(date1) ORDER BY WEEK_IN_YEAR(date)

 

I get 2 results one for previous week (WEEK_IN_MONTH = 2, WEEK_IN_YEAR = 28) and for current week (WEEK_IN_MONTH = 3, WEEK_IN_YEAR = 28). Why I get week in month 28 for current week, becasue today(2013-07-16 is 29th week in year).

In documentation http://www.salesforce.com/us/developer/docs/dbcom_soql_sosl/index_Left.htm#StartTopic=Content/sforce_api_calls_soql_select_date_functions.htm is written:' The first week is from January 1 through January 7.' so it is not calendar week in year? just they calculate week by math? (numbers of days devided by 7? ).

 

In my opinion it should work like calendar week in year.

Ashish_SFDCAshish_SFDC

Hi Maros, 

 

Week_In_Month returns the week number as below, 

WEEK_IN_YEAR() | Returns a number representing the week in the year for a date field. | 1 for January 3 http://www.salesforce.com/us/developer/docs/dbcom_soql_sosl/index_Left.htm#StartTopic=Content/sforce_api_calls_soql_select_date_functions.htm

 

You got 28 as we were in the 28th week in the year. 

 

If you wish to get calendar weeks try the links below, 

http://harshesh7487.blogspot.in/2011/04/calculate-number-of-week-from-date-in.html

https://success.salesforce.com/answers?id=90630000000gmWBAAY

http://boards.developerforce.com/t5/Formulas-Validation-Rules/Work-Week-Number-Within-a-Year-Date-Calculation/td-p/143818

http://boards.developerforce.com/t5/Formulas-Validation-Rules/Need-help-writing-formula-to-display-Week-number-based-on-Date/td-p/124099

 

Regards,

Ashish

If your question is answered, please mark this post as Solved. 

 
Maros SitkoMaros Sitko

we are in 28th week?

when I wrote previous message, it was 16.jul 2013 what is 29th week (http://whatweekisit.com/). But SQOL method WEEK_IN_YEAR return 28.

16.jul 2013 is 197th day of year => 197/7 = 28.14 => probably this is result of WEEK_IN_YEAR. So in my opinion, it is not returning calendar week of year.

Am I right?

 

Ashish I was looking for function in soql, because I need group by week of year - I resolve my issue in other way, but this discussion is only about WEEK_IN_YEAR, I am not looking for solution of any issue, but I appreciate your help and suggestions
Ashish_SFDCAshish_SFDC

Hi Maros, 

 

Thanks for getting back, 

Yes, you are right it is retunring the week of year by dividing by 7 and not the calender year. 

 

Regards,

Ashish

If your question is answered, please mark this post as solved. 

 
Ashish_SFDCAshish_SFDC

Hi Maros,

 

Thank you for posting this idea, sharing here so that community members and visitors can vote for it.

https://success.salesforce.com/ideaView?id=08730000000kt9PAAQ

 

Regards,

Ashish