On Jun 1, 5:01 pm, Michael Moore <michaeljmo...@gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
As far as I know there is no 1000 year rule for leap years; if there
were then 3000 would be a leap year but it isn't:
select to_date('3000-02-29', 'RRRR-MM-DD') dt from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified
1000 was a leap year but it was determined by the old "divide by 4 and
if the result is even it's a leap year" rule.
David Fitzjarrell
--
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
