Business Objects (Oracle) returns wrong day of week

Business Objects (BO) is used to generate a report showing the schedule of employees. In the schedule the day of week is displayed as a header, which makes it easy for the employees to read. The data is retrieved from an Oracle database server.

There are two situations, one with a native Oracle installation and one with a sequenced Oracle installation.

In one situation the week starts on Sunday (zondag) in the other on Monday (maandag), resulting in a vague schedule for the employee. Although the date is correct, the corresponding day of week isn’t.

Determing day of week (DOW)

The day of week (DOW) is determined using the TO_CHAR function and the ‘D’ parameter. This results in a numerical result, which represents the day of week. In the BO report a translation from the numerical value to a dutch day of week has been made.

CASE TO_CHAR(MA.STRT,'D')
  WHEN '1' THEN 'Zondag'
    WHEN '2' THEN 'Maandag'
    WHEN '3' THEN 'Dinsdag'
    WHEN '4' THEN 'Woensdag'
    WHEN '5' THEN 'Donderdag'
    WHEN '6' THEN 'Vrijdag'
    WHEN '7' THEN 'Zaterdag'
    ELSE ''
  END  "Dag

In order to ‘solve’ the problem with the wrong day of week, the report operator changed the query. Although this results in the correct day of week, it isn’t consistent on all environments.

  CASE TO_CHAR(MA.STRT,'D')
  WHEN '7' THEN 'Zondag'
    WHEN '1' THEN 'Maandag'
    WHEN '2' THEN 'Dinsdag'
    WHEN '3' THEN 'Woensdag'
    WHEN '4' THEN 'Donderdag'
    WHEN '5' THEN 'Vrijdag'
    WHEN '6' THEN 'Zaterdag'
    ELSE ''
  END  "Dag"

Regional settings

The number returned by to_char(some_date,’D’) varies with NLS_TERRITORY. The behaviour of to_char and to_nchar can be influenced by setting the NLS_DATE_LANGUAGE, NLS_CALENDAR, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_DUAL_CURRENCY and NLS_ISO_CURRENCY NLS parameters.

The NLS_LANG is set per machine in the registry on the location HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient10g_home1\NLS_LANG.

Configuration differences

The Oracle installation in the sequence (containing all other applications) is configured with the ‘AMERICAN_AMERICA” language.

The NLS_LANG on the native installed Oracle client is ‘DUTCH_THE NETHERLANDS’.

According to the Oracle ® Database Client Installation Guide, the locale setting of your operating system determines the value of the NLS_LANG parameter at installation.

Solution

So, the “problem” is caused by an inconsistent NLS_LANG and a Business Object report that assumes a certain syntax. The problem can be solved by setting the NLS_LANG in the sequenced version and the native installation to the same language code.

An alternative could be to hardcode the NLS in the SQL function. The third parameter in the TO_CHAR function is the nls_date_language. This way the results is consistent, no matter what NLS_LANG is set on the client. The parameters are described in the Oracle ® Database Globalization Support Guide

Ingmar Verheij