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
