Following query returns different results when run as SQL Passthrough in WebFOCUS and when run directly in database.
Select
SYSDATE,TRUNC(SYSDATE-1, 'DAY') D1,TRUNC(SYSDATE, 'DAY') D2,TRUNC(SYSDATE, 'DAY')-7
FROM DUAL
Not able to find the reason for the same.

Regards,
Vivek
Please suggest

This message has been edited. Last edited by: <Kathryn Henning>,

What difference did you notice?
I could see in DB SYSDATE returns 11-FEB-15, where as result of SQL Passthru it is 2015/02/11 along with timestamp.

If you need to see 11-FEB-15 in WebFOCUS, then use TO_CHAR(SYSYDATE) in your sql query.

SELECT
TO_CHAR(SYSDATE),to_char(TRUNC(SYSDATE-1, 'DAY')) D1,to_char(TRUNC(SYSDATE, 'DAY')) D2,to_char(TRUNC(SYSDATE, 'DAY')-7)
FROM DUAL
;

Hope this helps.

Thanks,
Ram

Most likely there's a time-zone difference or another formatting difference between the client you use to execute SQL directly on the database and the Oracle instant-client -> WebFOCUS Oracle adapter -> WebFOCUS server route and its locale settings.

From my experience, Oracle has a rather confusing way to interpret client locale settings in SQL queries. I've never really managed to figure out what it's using when and why. I suspect the Oracle developers are equally confused about the matter. For the record, I haven't seen issues similar to these with other RDBMSes that are capable of handling client locales.

quote:

Originally posted by Ram Prasad E:
If you need to see 11-FEB-15 in WebFOCUS, then use TO_CHAR(SYSYDATE) in your sql query.

The trouble with using to_char is that you end up with an alphanumeric field instead of a smart datetime field. If you only need the output that's fine, but if you need to do any calculations on those date-fields that's not a convenient data type.

The difference which i am facing is in the field D2.
In DB it is comming as 09-FEB-15
while when the query is run in WebFOCUS the value is comming as 08-FEB-15.

I am not able to figure out if it is some environment setting.

quote:

The trouble with using to_char is that you end up with an alphanumeric field

Yes you are correct.

Vivek, It looks like timezone issue. Ensure your ORA Client and WF Connected ORA Client are in same time zone. Also make sure you are connecting to ORA server in same timezone.

Thanks,
Ram

You'll probably see a difference in time between the dates from both sources. You can verify that using:

select to_char(trunc(sysdate -1, 'DAY'), 'DD-MON-YYYY HH24') from dual;

The difference will be the time-zone offset you'll need to correct for somewhere in the WebFOCUS SQL passthru path. ISTR that there's an ORA_ environment variable that needs to be set on the WebFOCUS server (where your Oracle adapter lives).

Wep/jvb,

sysdate wont create any difference in date time due to the timezone of the database vs the timezone of the client (WF). sysdate is always the Database DateTime in the database timezone. current_date is an oracle function that will adjust the Date Time result according to the client connection to the database.

The issue here is the trunc function. The issue is that truncate is assuming 2 different starting weekdays. When he is on the database, it is assuming Monday, when accessing through WF, it is assuming Sunday. Oracle has this to say about how the starting week day is determined

quote:

The starting day of the week used by the format models DAY, DY, and D is specified implicitly by the initialization parameter NLS_TERRITORY.

http://docs.oracle.com/cd/B193...ions230.htm#i1002084

The WF RS and Oracle DB server must be set to 2 different NLS_TERRITORY's creating the difference in results.

Quite possibly TRUNC is what's causing the issue, as you say, but how would the starting day of the week have any influence on the time zone offset? That is, assuming that a time zone offset problem is indeed the cause of the 1-day shift in TRUNC output. I don't see how that could be explained by a starting day of the week difference when TRUNC is rounding to DAY (and not WEEK).

Oddly enough, we discovered a similar issue in Firefox's Date class just this week - it's returning dates in CEST here, while in fact we are at CET (no daylight saving time this time of year).

Wep,

The issue has nothing to do with time zones. This is the NLS_TERRITORY setting. Run this for statements in order

alter session set nls_territory = 'America';

Select
SYSDATE,TRUNC(SYSDATE-1, 'DAY') D1,TRUNC(SYSDATE, 'DAY') D2,TRUNC(SYSDATE, 'DAY')-7
FROM DUAL;

alter session set nls_territory = 'Denmark';

Select
SYSDATE,TRUNC(SYSDATE-1, 'DAY') D1,TRUNC(SYSDATE, 'DAY') D2,TRUNC(SYSDATE, 'DAY')-7
FROM DUAL;  

It will illustrate the point.

Oracle will use the NLS_TERRITORY of the session to define which day of the week is the first day of the week. This is based on the session so it gets set by the client connecting to the database. In the case of WebFOCUS thats the Reporting Server. If he is using Sql Plus on the Database server it will be the nls_territory of the Database Server. If he is using Toad or SQL Developer, or some other program from his local machine to connect to the DB, then it will be the nls territory of his local machine.

Eric,

If the issue is due to first day of the week, then this problem exists when using TRUNC on dates with weekday as Sunday/Monday.

Let me check in my machine as well. Thanks for more detailed explanation Eric.

quote:

Originally posted by eric.woerle:
The issue has nothing to do with time zones.

alter session set nls_territory = 'America';
alter session set nls_territory = 'Denmark';

It will illustrate the point.

Well yeah, obviously there's a 7hr or 8hr time zone difference between those territories and possibly a DST offset difference. That's why the dates are different, which is what I was saying.

Dates don't magically change when you have different language settings, but they do change when you look at them from a different time zone (implying a different location), from a different time of the year (different DST) or a combination of the two and that is entirely due to time zones.

Sure, Oracle's NLS_TERRITORY is a client setting, but that's how the Oracle server knows to which time zone to translate a date in the database to present it correctly to the client - unless the client specified that it wanted a date at a specific time zone (say UTC), of course. The setting also seems to specify how to format the date, but that is beside the point here.

And it is quite likely that the NLS_TERRITORY setting is the source of the problem that the OP tries to get solved too; basically we're just saying the same thing in different ways.

Wep,

NLS_TERRITORY does not impact timezones. In my example the first field is SYSDATE. you will notice that SYSDATE doesn't change time zone. CURRENT_DATE will adjust for Time Zones, Sysdate will not.

Run these statements. It will illustrate the difference between sysdae and current date

 ALTER SESSION SET TIME_ZONE  = '-0:00';
 
select sysdate, current_date from dual;

ALTER SESSION SET TIME_ZONE  = '-7:00';

select sysdate, current_date from dual;

Notice how sysdate never changes but current date does. This is your time_zone setting. This is completely different from NLS_TERRITORY.

Oracle says the following about sysdate

quote:

Purpose

SYSDATE returns the current date and time set for the operating system on which the database resides. The datatype of the returned value is DATE, and the format returned depends on the value of the NLS_DATE_FORMAT initialization parameter. The function requires no arguments. In distributed SQL statements, this function returns the date and time set for the operating system of your local database. You cannot use this function in the condition of a CHECK constraint.

http://docs.oracle.com/cd/B193...200/functions172.htm

Timezones will not affect sysdate.

Oracle says the following about current_date

quote:

Purpose

CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.

Examples

The following example illustrates that CURRENT_DATE is sensitive to the session time zone:

http://docs.oracle.com/cd/B193...200/functions036.htm

ALTER SESSION SET TIME_ZONE = '-5:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-05:00          29-MAY-2000 13:14:03

ALTER SESSION SET TIME_ZONE = '-8:0';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-08:00          29-MAY-2000 10:14:33
  

The oracle documentation says the following about NLS_TERRITORY

quote:

NLS_TERRITORY specifies the name of the territory whose conventions are to be followed for day and week numbering.

This parameter also establishes the default date format, the default decimal character and group separator, and the default ISO and local currency symbols.

http://docs.oracle.com/cd/B193...37/initparams131.htm

That is not timezone.

Oracle says this about Time Zone

quote:

Specifying the Database Time Zone and Time Zone File

You can specify the database time zone and the supporting time zone file.


Setting the Database Time Zone

Set the database time zone when the database is created by using the SET TIME_ZONE clause of the CREATE DATABASE statement. If you do not set the database time zone, then it defaults to the time zone of the server's operating system.

You can change the database time zone for a session by using the SET TIME_ZONE clause of the ALTER SESSION statement.

http://docs.oracle.com/cd/B193...create.htm#sthref325

In your response you say

quote:

Dates don't magically change when you have different language settings,

That is correct, they don't change from a language setting. But we aren't using a language setting. We are using a territory setting. As I mentioned above and as stated in the Linked Oracle document above, the TRUNC() function in Oracle defines the starting day of the week by the NLS_TERRITORY setting. In other words, one territory uses sunday as the first day of the week and another territory uses Monday. the function TRUNC(date,'DAY') is the FOCUS equivalent of DATEMOV(date,'BOW'). FOCUS has a set command that allows you to define your work week and the first day of the week, Oracle uses the NLS_TERRITORY setting.

You go on to say

quote:

but they do change when you look at them from a different time zone (implying a different location), from a different time of the year (different DST) or a combination of the two and that is entirely due to time zones.

yes timezones come into play with CERTAIN timestamps. But as I showed above, sysdate does not adjust to the session timezone. It is consistently the date time in the timezone of the database regardless of the clients timezone. Timezone is also not applied to regulare date/time and timestamp fields. It must be either a timestamp with local timezone or timestamp with timezone. Oracle documents this data types quite extensively

http://docs.oracle.com/cd/B193...4225/ch4datetime.htm

Next you say this about NLS_TERRITORY

quote:

Sure, Oracle's NLS_TERRITORY is a client setting, but that's how the Oracle server knows to which time zone to translate a date in the database to present it correctly to the client - unless the client specified that it wanted a date at a specific time zone (say UTC), of course. The setting also seems to specify how to format the date, but that is beside the point here.

This is just false. NLS_TERRITORY has nothing to do with timezone. The database timezone is either explicitly set on the database, or it is by default set to the time zone of the database server. In no instance does the NLS_TERRITORY dictate a time zone.

Furthermore I can have my NLS_TERRITORY set to say America and my Timezone set to IST. Obviously IST never occurs in America. But since these are defining two different sets of things, I can do this.

Unfortunately we are not saying the same thing, and continuing to insist that this has anything to do with timezones will just further confuse people who are not familiar with how the Oracle Database actually functions and how to resolve the issue that jvb has encountered. For him to resolve his issue, so that he gets the same result from the trunc function in WebFOCUS as he does when executing from his database, he needs to find out what territory his database server is set in and either update his RS to use the same NLS_TERRITORY setting OR to do place the alter session set NLS_TERRITORY command into his sql query and set it to the territory of the database.

to identify his NLS_TERRITORY, and other NLS settings he can use the following statement

  
SELECT * from NLS_SESSION_PARAMETERS;

quote:

That is correct, they don't change from a language setting. But we aren't using a language setting. We are using a territory setting. As I mentioned above and as stated in the Linked Oracle document above, the TRUNC() function in Oracle defines the starting day of the week by the NLS_TERRITORY setting. In other words, one territory uses sunday as the first day of the week and another territory uses Monday. the function TRUNC(date,'DAY') is the FOCUS equivalent of DATEMOV(date,'BOW'). FOCUS has a set command that allows you to define your work week and the first day of the week, Oracle uses the NLS_TERRITORY setting.

Aha, There's the source of our confusion! TRUNC(date, 'DAY') truncates a date to a day, at midnight. It does not truncate the date to the beginning of the week like DATEMOV(date,'BOW') does. Remember, the DATE type stores both date and time!

That's why I keep saying that the setting for first-day-of-the-week (however it works in Oracle) is not relevant to the OP's problem and that we're dealing with an implicit time zone conversion here.

With respect to NLS_TERRITORY, it seems that I was wrong about that involving a time zone, as your examples prove. Apparently it's really just the formatting of the date.

You are also correct that SYSDATE returns a date regardless of time zone: It returns the date at the time zone of the server, not of the client.

However, once TRUNC had it's way with that date, apparently it returns it at the client time zone.

I'm not sure how Oracle converts server dates to client dates, seeing as (according to the documentation) the DATE type has no support for DST or time zones. For that arithmetic you're supposed to use TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE respectively.

It would appear that TRUNC(date, field) either returns a TIMESTAMP WITH TIME ZONE or uses one internally for the conversion and by doing so converts the server-local SYSDATE to a client-local date.

And that's how the OP was seeing dates different from what he expected.

quote:

Aha, There's the source of our confusion! TRUNC(date, 'DAY') truncates a date to a day, at midnight. It does not truncate the date to the beginning of the week like DATEMOV(date,'BOW') does. Remember, the DATE type stores both date and time!

Scratch that, Oracle is weird in that way apparently. TRUNC(date, 'DAY') does truncate to the starting day of the week. To truncate to day at midnight you use TRUNC(date, 'DD').

Subtle difference, big consequences - Looks like I'll need to do a searh for TRUNC, as I use that to make Oracle timestamps WebFOCUS compatible.

Glad I didn't cut this discussion short!