Business Objects (Oracle) returns wrong day of week

Written by Ingmar Verheij on June 7th, 2011. Posted in Microsoft App-V

Days of the 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.

  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.

  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.



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

Ingmar Verheij

At the time Ingmar wrote this article he worked for PepperByte as a Senior Consultant (up to May 2014). His work consisted of designing, migrating and troubleshooting Microsoft and Citrix infrastructures. He was working with technologies like Microsoft RDS, user environment management and (performance) monitoring. Ingmar is User Group leader of the Dutch Citrix User Group (DuCUG). RES Software named Ingmar RES Software Valued Professional in 2014.

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Tags: , ,

Trackback from your site.

Comments (1)

Leave a comment



%d bloggers like this: