Avatar of seenall
seenall
 asked on

Oracle Date Query - different Results depending on Host OS running DB Connection

Hello

After some research (probably from another EE page) I managed to construct a query that shows the dates of the first and last day of the week week for the past three weeks and current week

The working week in my scenario is Sunday to Sunday.

The query Q1 works exactly as required when executed via SQL*Plus on a UNIX client
But in windows (the final destination of the impementation) - any client connection (TOAD, SQL*Plus SRSS) the dates are shifted to the right by one.

Okay my obvious fix is to adjust lines 2 and 3 to report a day behind to pull the result set as expected (Q2) - but this leads to inconsistent developments and integrations

Has anyone come across this before?  Did they get an idea why this happens?

Thanks in advance
Q1
select rank() over (partition by period order by last_sunday) weekago, last_sunday, next_saturday from
(select 'week' period, trunc(sysdate, 'day') + -7 - (p.pivot*7) last_sunday
     , trunc(sysdate, 'day') + -1 - (p.pivot*7) next_saturday
  from dual d
     , (select rownum pivot
        from all_objects
       where rownum < 3) p
union
select 'week' period, trunc(sysdate, 'day') + 7 - (p.pivot*7) last_sunday
     , trunc(sysdate, 'day') + 13 - (p.pivot*7) next_saturday
  from dual d
     , (select rownum pivot
        from all_objects
       where rownum < 3) p
) order by 1 asc
/

RESULTS on SQL*Plus via UNIX - Results as I would expect them

   WEEKAGO LAST_SUND NEXT_SATU
---------- --------- ---------
         1 24-JAN-10 30-JAN-10
         2 31-JAN-10 06-FEB-10
         3 07-FEB-10 13-FEB-10
         4 14-FEB-10 20-FEB-10

RESULTS from SQL*Plus via Windows (and SSRS and Toad)
shifts one day to right

   WEEKAGO LAST_SUND NEXT_SATU
---------- --------- ---------
         1 25-JAN-10 31-JAN-10
         2 01-FEB-10 07-FEB-10
         3 08-FEB-10 14-FEB-10
         4 15-FEB-10 21-FEB-10

Resolved in windows clients with the following (lines 2&3 and 9&10 Adjusted)
Q2
select rank() over (partition by period order by last_sunday) weekago, last_sunday, next_saturday from
(select 'week' period, trunc(sysdate, 'day') + -8 - (p.pivot*7) last_sunday
     , trunc(sysdate, 'day') + -2 - (p.pivot*7) next_saturday
  from dual d
     , (select rownum pivot
        from all_objects
       where rownum < 3) p
union
select 'week' period, trunc(sysdate, 'day') + 6 - (p.pivot*7) last_sunday
     , trunc(sysdate, 'day') + 12 - (p.pivot*7) next_saturday
  from dual d
     , (select rownum pivot
        from all_objects
       where rownum < 3) p
) order by 1 asc
/
   WEEKAGO LAST_SUND NEXT_SATU
---------- --------- ---------
         1 24-JAN-10 30-JAN-10
         2 31-JAN-10 06-FEB-10
         3 07-FEB-10 13-FEB-10
         4 14-FEB-10 20-FEB-10

Open in new window

Oracle DatabaseSSRSSQL

Avatar of undefined
Last Comment
seenall

8/22/2022 - Mon
seenall

ASKER
Correction :

FROM (Incorrect) The working week in my scenario is Sunday to Sunday.
TO (Correct) The working week in my scenario is Sunday to Saturday.

Sean Stuber

is the SQL Server zone correct? Looks like you are asking about Oracle
SOLUTION
gatorvip

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
seenall

ASKER
sdstuber : please see comments I make below for gatorvip and if you have any thoughts on the underlying issue I will be grateful

gatorvip: Thanks - after switching some of the NLS parameters at session level I deduced our UNIX server is configured with AMERICA (specifically NLS_Territory) and the Windows Server came in with UNITED KINGDOM

Changing the Windows Server to AMERICA for NLS_Territory corrected the issue and Q1 returned identical results to that returned from the UNIX Server

Question remains - is it even possible to over-ride the NLS parameters at session level from an SRSS connection?  Attempted to find a setting in the Data Source - Also Tried (from SRSS Tools Options > Environment -> International Settings but there is only 2 options here - English or "Same as Microsoft Windows"

Now I can go about changing the global environment settings on both Servers to align - but this might create me a headache as both are production servers hauling out more reports than I care to mention - I mean to say I won't be employed long if the statistics in reports fall out of wack because the clause trunc(sysdate) is everywhere amongst the report

I also Asked MR.G (google) for thoughts on SRSS and NLS and he came up blank too.  If there is any other way to change NLS settings at session level created by SRSS that would be ideal

Thanks again
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Sean Stuber

seenall, it still looks like you only want an Oracle answer.  If so I will rezone the question for you.  If you do want an answer for SQL Server as well, I will leave it alone.
seenall

ASKER
sdstuber - Your input is really appreciated - the question is how to make an SRSS session adjust the NLS session parameters when running a report ?

Any thoughts ?
Sean Stuber

does that mean you want an Oracle answer only or not?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Sean Stuber

I keep asking that same question repeatedly because I want to know how to best answer your question.

If I give you an answer that will only work in Oracle but you need to use SQL Server too, then that's not helpful.
Same as if I give you a SQL Server answer if you need Oracle.

If you need both, please say so.
If you need only one, please say so.

Thanks
SOLUTION
gatorvip

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
seenall

ASKER
gatorvip: Thanks for the response

Environment Clarification
DB Oracle
Host Sun UNIX (version I am not familiar)

BI Server 1 - Linux Debian - using shell scripts , SQL Plus and PERL to email results
BI Server 2 - Windows Server 2003 - with SQL Server Reporting Services using Microsoft end  to end stack (OS,Internet Service, Report Server, Report Manager)

Both BI Servers are connecting to the same DB Host (Sun / Oracle 10g) over Oracle tnsnames

The question (identified through this process) relates to the mix of environments and the different result set being returned from the Oracle SQL Query.

The function you kindly crafted goes a long way and I will certainly give it serious consideration when weighing up the options

seenall

ASKER
sdstuber: - please see previous post - The question is not related SQL Server Database, but to the SQL Server Reporting Services, and how to over-ride session parameters.

All responses much appreciated
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SOLUTION
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
seenall

ASKER
So as a summary

It seems there does not appear to be way to over-ride DB connection session parameters from SSRS when connecting to Oracle DB over TNSNAMES

In order to standardise the query across environments a Database Server side function is necessary.

This is great - thanks.  It is a shame that the logic of the report in its entirety cannot be written into a single code library and deployment.  A standard procedure with most reporting applications I guess.

Thanks for all input
Sean Stuber

My last query should be consistent from any client and doesn't require a function to be written on the server side.
And, for this query there doesn't appear to be any "session" information required.  Or maybe I'm misunderstanding your requirement.
It seems like pretty basic sql
seenall

ASKER
Thank you
for patience
and understanding

This works a treat !  Wish I had awarded more points when posing the question - my bad
Best regards
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Sean Stuber

glad we could help,
also you can change points for a question until you close it.
After you close it a moderator or advisor can change the points too.  I "could" but since I'm participating in the question it would be inappropriate for me to do so.
Also, it's unnecessary unless gatorvip disagrees.
gatorvip

Re: #26584868

Cool, the next_day() definitely cleans it up. I thought that function was also depending on the environment so I didn't use it, but I checked the docs and it does work.
seenall

ASKER
Update

I had to adjust the query slightly because when it was run on a sunday, the dates pulled back returned a start and end period with  14 days between.  Only on Sundays. Unfortunately this was the day the scheduled report was due to run

So I introduced a check on the 'Day' parameter of the date, checked if it was Sunday and set the date to work from to Sysdate+1

Example below
select * from ( SELECT 5 - n weekago,
       NEXT_DAY(d - 8 - (n * 7), 'Sunday') last_sunday,
       NEXT_DAY(d - 1 - (n * 7), 'Saturday') next_saturday
FROM (select 5 - level n
     , trunc(case when rtrim(to_char(sysdate,'Day')) = 'Sunday' then sysdate+1 else sysdate end) d
      FROM DUAL
      CONNECT BY LEVEL <= 5)
ORDER BY 5 - n )

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61