week numbers display unexpected results mystery, locale?

joben

New year, new opportunities 😃

I am trying to figure out why the WEEK function is not behaving as expected, while the CURRENT_WEEK functions seems to return the expected result.

For instance, if I do like this:
WEEK(Bo.StartTime)
It will return 2 (using todays date, 2021-01-04)

If I do like this:
CURRENT_WEEK
It will return 1 (today, the date is 2021-01-04)

I ran a list of dates through the WEEK function and I have come up with a table of week numbers, and also the value I expected:

Here is a snippet of the week numbers as seen in an Outlook calendar for reference (starts with monday, week numbers in the left column):

As it turns out, there are different ways of counting week numbers :roll:
I found this website where I compared the Swedish way of counting to Australia.
However, this doesn't explain why CURRENT_WEEK is displaying the week correctly.

Doesn't the WEEK function respect the locale?

PointsWell

HNY.

I read an article related to the presentation of time and how that affects week numbers. I will try to find it, but from memory there is an impact of upper case Y and lower case y. If you use uppercase it will generate an incorrect number of weeks - yyyy = calendar year whereas YYYY = week year.

This article explains in a high level https://medium.com/@shihab.buet.cse07/difference-between-yyyy-and-yyyy-in-date-format-be5a827603c

The one I remember reading was more involved, but long and the short of it - check the date formats.

joben

Thanks for the info regarding calendar year and week year.

I have been using this timestamp format the whole time: yyyy-MM-dd HH:mm so that should be correct.
Using YYYY doesn't work at all.

I also discovered that the WEEK function is working when I use CURRENT_DATE or CURRENT_TIMESTAMP as input:

I will do some experimenting and report back.

joben

So I noticed some inconsistencies when troubleshooting, and I'm still confused.

If I display WEEK(Bo.StartTime) in a DISPLAY MESSAGE window (like the image above), I get the correct value.
If I display WEEK(Bo.StartTime) in a grid inside a form as a calculated value, I get the incorrect value.
If I make a query like FIND Bo WHERE( WEEK(Bo.StartTime=CURRENT_WEEK) ) I don't find the correct lines.
If I run a process or update rule that calculates the number of BO lines WHERE( WEEK(Bo.StartTime=CURRENT_WEEK)) and saves it into an attribute of the LIRU, I don't get the expected value.

How I circumvented it:

I Created a number attribute like: Bo.WeekNumber

Then I used an update rule like: Bo.WeekNumber=WEEK(Bo.StartTime).
I even built a process that can set the value correctly for the old lines in the BO.

Now I have a functional query like FIND Bo WHERE( Bo.WeekNumber=CURRENT_WEEK) which displays the lines I'm interested in.

All this trouble probably has a logical explanation.