week numbers display unexpected results mystery, locale?

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
joben
Posts: 221
Joined: Wed Nov 06, 2019 9:49 pm
Location: Sweden
Contact:

week numbers display unexpected results mystery, locale?

Post by joben »

New year, new opportunities :D

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:
weeks.png
weeks.png (9.87 KiB) Viewed 2448 times
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):
weeks2.png
weeks2.png (13.51 KiB) Viewed 2448 times
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?
Regards, Joakim

Image
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: week numbers display unexpected results mystery, locale?

Post by 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/d ... e5a827603c

The one I remember reading was more involved, but long and the short of it - check the date formats.
joben
Posts: 221
Joined: Wed Nov 06, 2019 9:49 pm
Location: Sweden
Contact:

Re: week numbers display unexpected results mystery, locale?

Post by 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:
weeks3.png
weeks3.png (10.71 KiB) Viewed 2418 times
I will do some experimenting and report back.
Regards, Joakim

Image
joben
Posts: 221
Joined: Wed Nov 06, 2019 9:49 pm
Location: Sweden
Contact:

Re: week numbers display unexpected results mystery, locale?

Post by 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.
Regards, Joakim

Image
Post Reply