Leap Year Coding

[Yuvaraj Sundaresan]

Hi Friends,

    I need to print the no of days(365/366) based on my input. For example

From Date         To Date            Output Should Be        

(Input 1)              (Input 2)

01/04/2011          31/03/2012       366

01/04/2011          15/02/2012       365(Since the To date is falling in Leap year but the To date is less than 29/FEB/2012 so it should print as 365)

01/04/2012           31/03/2013      365

01/01/2012           30/07/2012      366

Kindly suggest me how it can be achievable.

Regards

Yuvaraj

[Michael Moore]

How is that 365 days?

SQL> SELECT TO_DATE ('15/02/2012', 'dd/mm/yyyy') 

          - TO_DATE ('01/04/2011', 'dd/mm/yyyy') days

  FROM DUAL

      DAYS

----------

       320

1 row selected.

Looks like 320 days to me.

Mike

--
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...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en

[Yuvaraj Sundaresan]

Hi Michel

    365 is not the difference of two dates total number of days of that year

[Michael Moore]

Which year, 2012 or 2011?

[Yuvaraj Sundaresan]

2012 should be 365 because he to date is '15/02/2012'  doesnt fall after 29/02/2011

[Yuvaraj Sundaresan]

Normally 2012 is a leap year so the total number of days will be 366 but according to our input the To date doesnt fall after 29/02/2012 so it should return 365.

[Michael Moore]

you should use a variable instead of hardcoding the date as I have done.

SELECT ADD_MONTHS (TRUNC (TO_DATE ('15/02/2012', 'dd/mm/yyyy'), 'yyyy'), 12)

       - TRUNC (TO_DATE ('15/02/2012', 'dd/mm/yyyy'), 'yyyy') -- days in this year

       - CASE

            WHEN ADD_MONTHS (TRUNC (TO_DATE ('15/02/2012', 'dd/mm/yyyy'), 'yyyy'), 2) =

                    TRUNC (TO_DATE ('15/02/2012', 'dd/mm/yyyy'), 'yyyy') + 60 -- this is a leap year

                 AND TO_DATE ('15/02/2012', 'dd/mm/yyyy') <                   --

                        TRUNC (TO_DATE ('15/02/2012', 'dd/mm/yyyy'), 'yyyy') + 59  -- target date is before feb 29

            THEN

               1

            ELSE

               0

         END

          days

  FROM DUAL;

[rich p]

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

[Michael Moore]

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

--

[ddf]

> > *for a leap year*:

> > select to_date('29-FEB-2012','DD-MON-YYYY') from dual
>
> > output: 2/29/2012
>

> > *for a non-leap year*:

> >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