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
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?
week numbers display unexpected results mystery, locale?
-
- Posts: 1457
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: week numbers display unexpected results mystery, locale?
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.
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.
Re: week numbers display unexpected results mystery, locale?
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.
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.
Re: week numbers display unexpected results mystery, locale?
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.
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.