Hi Rich, I too was at a loss as to the significance of "From Date". It seems to have nothing to do with the output. I used Oracle's date functions to avoid the "100" and "400" exceptions to the rule of mod/4. Then there is also the 1000 year exception to the 400 year exception.
I count on the fact that ADD_MONTHS always knows how many days are in Feb. Regards, Mike On Wed, Jun 1, 2011 at 4:46 PM, rich p <richg...@gmail.com> wrote: > Interesting challenge, although the requested output doesn't seem to have a > relation to the input values: "From Date" and "To Date"; what significance > does the "From Date" have (if any?) > > The great thing about SQL (and probably most everything else), there are > dozens of ways to approach the same problem! On my first approach, I lifted > an idea from Wikipedia: > > http://en.wikipedia.org/wiki/Leap_year > > Which also lists a simple algorithm for identifying the leap years. > Apparently you can apply the Modulus function to test if a year is a > leap-year: > > if year modulo 400 is 0 > then is_leap_year > else if year modulo 100 is 0 > then not_leap_year > else if year modulo 4 is 0 > then is_leap_year > else > not_leap_year > > The syntax for Oracle's Modulus is: MOD( num1, num2 ); where num1 is the > dividend and num2 is the divisor. The output is the modulus. > So for the example, > > select mod(2012, 400), mod(2012, 100), mod(2012, 4) from dual > yields: 12, 12 and 0 > respectively. > > ...or if you want to lean on Oracle's internal features, try this: > > *for a leap year*: > select to_date('29-FEB-2012','DD-MON-YYYY') from dual > > output: 2/29/2012 > > *for a non-leap year*: > select to_date('29-FEB-2012','DD-MON-YYYY') from dual > > output: ORA-01839: date not valid for month specified > > Which suggests that Oracle is also aware of leap years and will not accept > "nonsensical" dates (i.e., dates that do not exist); the error suggests that > you can also alternatively use a PL/SQL exception to figure out the > difference between a leap year and a non-leap year: > > declare > > date_not_valid exception; > v_input_year varchar2(4):= '2011'; -- substitute your year to test here > c_leap_date constant varchar2(6):= '29/02/'; > c_datemask constant varchar2(10):= 'dd/mm/yyyy'; > v_leap_out date; > > PRAGMA EXCEPTION_INIT(date_not_valid, -1839); > > begin > > v_leap_out := to_date( c_leap_date || v_input_year, c_datemask ); > dbms_output.put_line('leap year'); > > exception > > when date_not_valid then > dbms_output.put_line('NOT a leap year'); > > end; > > The input for this PL/SQL anonymous block is "v_input_year" which is a four > character string representing the year you wish to test. Replace the > "dbms_output" calls with your own subroutine or call that responds the way > you want it the output to behave. I had to associate an exception handle > with the resulting Oracle error code using the "exception" type and the > "exception_init" pragma. > > In conclusion, leveraging Oracle PL/SQL exception handling is another > approach to tackling the leap year problem. Try Googling "Oracle Exception > Handling" for more info and documentation on how to implement this type of > approach in your other PL/SQL projects. > > Richard > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
