Jump to content

Jasper servers show different date value from same Oracle database


Recommended Posts

Jasperserver 7.9 with Oracle 12.1

We have 2 Jasper servers (WS2019) pointing to the same database. Both servers have same timezone and locale (Windows and Jasper).

Each server returns a different value for the same date using trunc ( sysdate, 'D') to get the date of the Sunday of this week.  Also trunc ( sysdate - 7, 'D') for the previous Sunday.

Good server shows 2023-09-17 07:00:00.0  and 2023-09-24 07:00:00.0 

Bad server shows 2023-09-18 07:00:00.0  and 2023-09-25 07:00:00.0 

    07:00:00 as query adds (7/24) for 0700 (start of work day).

Alternative SQL like "TRUNC(NEXT_DAY(SYSDATE - 7, 'SUNDAY')) + INTERVAL '7' HOUR" works on both good and bad server.

I'd like to understand why this is happening in case other reports are affected.


Link to comment
Share on other sites

  • Replies 4
  • Created
  • Last Reply

Top Posters In This Topic

Hi Nick, Oracle by default derives the day of the week based on the nls_territory parameter settings  in nls_session_parameters table of the db. If the nls_language is America, the start of the week is Sunday, however, most places follow the ISO standards which is Monday.  Some Arab countries it is Saturday and Bangladesh it is Friday. If you want to change the start of week , you can write db functions to override that. If you want to write code irrespective of  nls_territory parameter , you can use as you mentioned like

TRUNC (dt)  - TRUNC (dt, 'IW')

Link to comment
Share on other sites

The Windows locale/region was set differently on the 2 servers. The server.log showed different values for "user.country" on startup. I explicity set the value in report-server.conf.bat to keep consistency

    set "JAVA_OPTS=%JAVA_OPTS% -Duser.country=GB"

After that all ok



Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...