![]() I wrote a query to do that, but it doens't always return the data I excpected. I want to select all rows from that table where the date is smaller or equal to a supplied date minus the number of working days. ![]() I have a table with a date and a number (working days) in it. ![]() ![]() I am currently working on 9.2i and I would like the following: I have used your query to generate a list of working days in order to select rows from a table. SQL> alter session set nls_territory='FRANCE' Ģ 3 to_char(premiere, 'D') as "Day of week (1-7)", SQL> alter session set nls_date_language='FRENCH' PREMIERE Day of week (1-7) Day of week AMERICAN Abbreviated name of day Abbreviated AMERICAN SQL> with dreamliner as (select to_date('07/08/07', 'MM/DD/YY') as premiere from dual)ģ to_char(premiere, 'D') as "Day of week (1-7)",Ĥ to_char(premiere, 'D', 'NLS_DATE_LANGUAGE=AMERICAN') as "Day of week AMERICAN",ĥ to_char(premiere, 'DY') as "Abbreviated name of day",Ħ to_char(premiere, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') as "Abbreviated AMERICAN" from dreamliner SQL> column "Abbreviated AMERICAN" format A20 SQL> column "Abbreviated name of day" format A25 SQL> column "Day of week AMERICAN" format A20 SQL> column "Day of week (1-7)" format A20 Therefore, the 'DY' option can be made independent of the NLS settings but the 'D' option cannot. NLS_DATE_LANGUAGE dictates the "Abbreviated name of day" (option 'DY'), NLS_TERRITORY dictates the first "Day of week (1-7)" (option 'D'). SQL> select ems_oracle.ADD_BUS_DAYS(to_date('', 'mm/dd/yyyy'), 5)įROM (select level rnum from dual connect by level <= 365 ) TNS for Solaris: Version 10.2.0.3.0 - Production Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi I notice that if I commented out the code for the holiday table it would return the correct answer. Ops$marcio:LX92> alter session set nls_date_format= 'dd/Mon/yyyy' Ģ ( select level rn from dual connect by level <= 365 )ģ select to_date(:dt,'mm/dd/yyyy') + max(rn), max(rn)Ħ and to_char(to_date(:dt,'mm/dd/yyyy')+rn,'d') not in (1,7) TNS for Linux: Version 9.2.0.8.0 - Production Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production Ops$marcio:LX92> select * from v$version I didn't use holiday's table since this is test, but looks like you have a bug in your code. WHERE HOLIDAY_OBSERVED = V_BUS_DAY + RNUM ) īobby, it worked out for me, (9208). I tested with today date () and adding 5 business day. Do you know why this would cause a different result? If this was in the production environment, it would be the reverse. If the table "all_objects" is chosen, the result is 7 which is correct. In our development server (Oracle 9.2), if the table "user_objects" is chosen the result is 545. After some investigation, I noticed that the function return different result I love your function and everything was going great until I made the change to my production environment. We simply take the biggest RNUM (from the inline view) and add it to the original date - we are done. As soon as ROWNUM in the outer query gets to be the number of business days we wanted to add - this query terminates. Each time we get a day that is not one of those days - ROWNUM in the outer query gets incremented by 1. Upon that set we start counting the number of days that are not sat and sun and not a holiday. So, now we just have a big set of numbers. Inline view does for us (all_objects is a good pick, it generally has thousands of rows in it). We start by creating a large set of numbers 1, 2, 3. He uses the same concept but does it a tad more efficiently. His query can do determinisically what mine "guessed" at. Table - insert into exclude_dates values ( '1' ) ĪND TO_CHAR(V_BUS_DAY + RNUM, 'DY' ) NOT IN ('SAT', 'SUN') He came up with a procedure that is about 2/3 times faster then my version and makes interesting use of ROWNUM. Thanks to Mike Clegg a Consultant specializing in Oracle, we have a better answer to this then the first one I gave.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |