Kirix Support Forums

WEEK function doesn't work properly

Please post any help questions, requests or other feedback here.

WEEK function doesn't work properly

Postby Francisco_Castro on Tue Mar 27, 2012 3:46 am


We just tried to use the WEEK function with a date field and we saw that it doesn't work properly because it gives you the week numeber for this year, but it does not returns you the correct week for a calendar.

For example, WEEK(DATE(2009,01,06)) = 1 but really the correct value it is 2, and it is because you take 1 to 7 like days of week. Will it be possible to find a solution for that?.

Thank you

Francisco Castro
ACFYD Análisis, SL
Registered User
Posts: 1
Joined: Tue Mar 27, 2012 3:33 am

Re: WEEK function doesn't work properly

Postby Aaron on Tue Mar 27, 2012 7:13 pm


The week function returns a value based off the number of days into the year; so days 1-7 are in the first week, 8-14 in the second week, and so forth.

However, you need the calendar week. In this case, when the first day of the year is on a Sunday, the absolute and calendar weeks are the same. When the first day of the year isn't on a Sunday, we can calculate the calendar week by accounting for the offset of the starting date, giving us a formula for the calendar week as follows:

week(7 - dayofweek(<date field>) + <date field>)

With the above formula, simply substitute your own date field into the <date field> parameter, and you'll have the calendar week for a particular date.

Aaron Williams
Kirix Support Team
User avatar
Kirix Support Team
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Return to Strata Help & Feedback