Weekday Numbers in Apex sensitive to Application Primary Language

The weekday in Apex is dependent on of the language set in the Application Properties. For that you cannot rely on TO_CHAR(datevalue,’d’), but should use TRUNC(datevalue)-TRUNC(datevalue,'iw')+1 to get ISO weekdays, that are independent of the language set. SQL Workshop did not show differences because the language for SQL Workshop remained the same. This made the analysis more difficult as the results of queries in Apex and SQL Workshop were inconsistent.

I noticed this after having changed the language from English (us) to Dutch(nl) in an application for registering the hours spent and declared on assignments. The screen for registering the declared shows an enterable fields for each day of week to accept the hours worked that day. This screen is showed in the picture below.

Before the change of language this page worked flawlessly. After having set the language to dutch the page did not function like I did before.
At the moment I have the luxury of working four days a week ;-), so I had filled in 8 hours for Monday up to Thursday. After saving this data the application returns to the menu. When I retrieved the page once more, I noticed that the screen showed Sunday up to Wednesday as days.
The data was stored using an offset from the first day of the ISO week. As the ISO week returns the same value regardless of the language the right data were saved. To retrieve the data a view is used in which I used SUM(DECODE(TO_CHAR(datefield,’d’)),2,hours) AS hours_monday to determine the number of hours worked on Monday. With the language set to English, this yielded the correct result. After the language was changed to Dutch, the value for Tuesday was retrieved, as tuesday is the second day in the ISO week.
The number of the weekday differs on the basis of the language. This may not be a surprise, but it was hard to find, because as it was a remote site I was using SQL Workshop to perform SQL statements to check. And apparently SQL Workshop is set to English, regardless of the language of the application (sounds logical). This meant that in SQL Workshop I still got the right results, while in the application, the days were shifted.
The solution was to calculate the weekday number by referring to the start of the ISO-week. The latter number is independent of the language used. We subtract the start of the ISO-week from a given date (use TRUNC to eliminate the time fraction) and add 1 to get the weekday number. The query

SELECT TRUNC(sysdate)-TRUNC(sysdate,'iw')+1 FROM dual

will yield the correct (ISO) day of the week.

As you may have noticed I am developing an Apex application for iPhone. For this I use iWebkit, which results in really nice and usable mobile applications. I hope to blog more about this in the near future.

Having fun Apexing