Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

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

Avatar of seenall
seenall asked on
Oracle DatabaseSSRSSQL
19 Comments1 Solution760 ViewsLast Modified:
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