Leap year function PLSQL

PL SQL Leap year function

Hi all, I didn't have time to write cause I had no time on my schedule :)
so I decided to refresh my blog with leap year calculator....

Some exceptions to this rule are required since the duration of a solar year is slightly less than 365.25 days. Over a period of four centuries, the accumulated error of adding a leap day every four years amounts to about three extra days. The Gregorian Calendar therefore omits 3 leap days every 400 years, omitting February 29 in the 3 century years (integer multiples of 100) that are not also integer multiples of 400.
For example, 1600 was a leap year, but 1700, 1800 and 1900 were not. Similarly, 2000 was a leap year, but 2100, 2200, and 2300 will not be. By this rule, the average number of days per year is 365 + 1/4 − 1/100 + 1/400 = 365.2425.

Why do we need Leap Years?

Leap Years are needed to keep our calendar in alignment with the Earth's revolutions around the sun. It takes the Earth approximately 365.242199 days – or 365 days, 5 hours, 48 minutes, and 46 seconds (a tropical year) – to circle once around the Sun.

However, the Gregorian calendar has only 365 days in a year, so if we didn't add a day on February 29 nearly every 4 years, we would lose almost six hours off our calendar every year. After only 100 years, our calendar would be off by approximately 24 days!
Which
The year is evenly divisible by 4;
If the year can be evenly divided by 100, it is NOT a leap year, unless;
The year is also evenly divisible by 400. Then it is a leap year.

Years are Leap Years?

In the Gregorian calendar 3 criteria must be taken into account to identify leap years:

This means that 2000 and 2400 are leap years, while 1800,1900,2100,2200,2300 and 2500 are NOT leap years.

The year 2000 was somewhat special as it was the first instance when the third criterion was used in most parts of the world since the transition from the Julian to the Gregorian Calendar

Why should I care about Leap Years?

The 29th day added to February  (leap day ) brings a smidgen of a financial boost for some consumers and slight adjustments for businesses that rely on daily sales and workers who are paid by the hour.
So if February is leaping then you must add +1 day to the monthly salary if you are accountant or to calculate extra profit for February....

Little function for leaping year

 -- function returns 1 for leaping year
 

FUNCTION f_leap_year (i_year VARCHAR2) RETURN NUMBER IS
v_remainder1 number(5,2);
v_remainder2 number(5,2);
v_remainder3 number(5,2);
o_value pls_integer;
begin
   v_remainder1 := mod(i_year,4);
   v_remainder2 := mod(i_year,100);
   v_remainder3 := mod(i_year,400);
   if ((v_remainder1 = 0 and v_remainder2 <> 0 ) or v_remainder3 = 0) then
      o_value := 1;
      return o_value;  
   else
      o_value := 0;
      return o_value;  
   end if;
end f_leap_year;

 enjoy
....................................................

Read More

PLSQL Operators

PL SQL Operators

Relational operators compare two expressions or values and return a Boolean result. PL/SQL supports operators like

=, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN

Operator	Meaning
=	equal to
<>, !=, ~=, ^=	not equal to
<	less than
>	greater than
<=	less than or equal to
>=	greater than or equal to

Is NULL operator checks if
Example code:

declare
   a number ( 2 ) := 50 ;
   b number ( 2 ) := 15 ;
begin
   if ( a = b ) then
      dbms_output.put_line ( '1-a is equal to b' );
   else
      dbms_output.put_line ( '1-a is not equal to b' );
   end if;
   if ( a < b ) then
      dbms_output.put_line ( '2-a is less than b' );
   else
      dbms_output.put_line ( '2-a is not less than b' );
   end if ;
   if ( a > b ) then
      dbms_output.put_line ( '3-a is greater than b' );
   else
      dbms_output.put_line ( '3-a is not greater than b' );
   end if ;
   -- Lets change value  a and b
   a      := 40 ;
   b      := 90 ;
   if ( a <= b ) then
      dbms_output.put_line ( '4-a is either equal or less than b' );
   end if ;
   if ( b >= a ) then
      dbms_output.put_line ( '5-b is either equal or greater than a' );
   end if ;
   if ( a <> b ) then
      dbms_output.put_line ( '6-a is not equal to b' );
   else
      dbms_output.put_line ( '6-a is equal to b' );
   end if ;
end;

Output:



IS NULL Operator

The IS NULL operator returns the Boolean value TRUE if its operand is null or FALSE if it is not null. test for the state of being null IF variable IS NULL THEN


LIKE Operator

You use the LIKE operator to compare a character, string, or CLOB value to a pattern. Case is significant. LIKE returns the Boolean value TRUE if the patterns match or FALSE if they do not match.

The patterns matched by LIKE can include two special-purpose characters underscore _ matches one character and percent sign % matches zero or more characters
example:
select var_name from table_names where var_name LIKE 'JOHN%';

you will get all names starting with JOHN like JOHN, JOHNSON,JOHNATHON etc...

BETWEEN Operator

The BETWEEN operator tests whether a value lies in a specified range. It means "greater than or equal to low value and less than or equal to high value."
Example:
If you want to select some record between some input date and system date

Select * from SOME_TABLE where to_char(beginning_date,'yyyymmdd') between to_char(i_first_date,'yyyymmdd') and to_char(sysdate,'yyyymmdd')

IN Operator

The IN operator tests set membership. It means "equal to any member of
 select * from table names where var_name IN ('JOHN','ERIC');
You will get all records containing names of JOHN and ERIC...

Read More

PLSQL what day is it?

PLSQL what day is it

Say that for some reason you need name of the day for certain reason...
You can create function that will return name of the day of specified date....

function f_name_of_the day (i_date) return varchar2 as
l_day varchar2(50):=null;
begin
   SELECT TO_CHAR( trunc(to_date( to_char(to_date(i_date,'YYYYMMDD')) )),'day') into l_day FROM DUAL;
   return(day);
end f_name_of_the day ;

So what does this function do?

You enter number in date format like 20130306 which is 2013 year 03 month 06 day and function should return Wednesday value....
You can manipulate with desired input value by changing format of date
 (to_date(i_date,'YYYYMMDD')) into lets say (to_date(i_date,'YYYYDDMM')) so that input now must be in year day month shape.

CODE in SQL developer

Here is  code for simple developer test that will display name of the day

declare
l_day varchar2(50):='20130306';
begin
   select to_char( trunc(to_date( to_char(to_date(l_day,'YYYYMMDD')) )),'day') into l_day from dual;
   dbms_output.put_line(l_day);
end;

Read More