Oracle PL/SQL/Numeric Math Functions/FLOOR

FLOOR: Returns the floor value (next lowest integer below number)

  

SQL>
SQL>
SQL> -- create demo table
SQL> create table TestTable(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    MyName             VARCHAR2(10 BYTE),
  4    MyDate             DATE,
  5    MyNumber           Number(8,2)
  6  )
  7  /
Table created.
SQL>
SQL>
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19960711","YYYYMMDD"),12.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("2","Jason",to_date("19970622","YYYYMMDD"),-12.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("3","Smith",to_date("19980513","YYYYMMDD"),22.1);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("4","Tailor",to_date("19990624","YYYYMMDD"),-2.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("5","Darlene",to_date("20000415","YYYYMMDD"),2.1);
1 row created.
SQL>
SQL>
SQL> select * from TestTable;
ID   MYNAME     MYDATE      MYNUMBER
---- ---------- --------- ----------
1    Alison     11-JUL-96      12.12
2    Jason      22-JUN-97     -12.12
3    Smith      13-MAY-98       22.1
4    Tailor     24-JUN-99      -2.12
5    Darlene    15-APR-00        2.1
SQL>
SQL>
SQL>
SQL> -- FLOOR: Returns the floor value (next lowest integer below number).
SQL>
SQL> select MyNumber, FLOOR(MyNumber) from TestTable;
  MYNUMBER FLOOR(MYNUMBER)
---------- ---------------
     12.12              12
    -12.12             -13
      22.1              22
     -2.12              -3
       2.1               2
SQL>
SQL>
SQL>
SQL> drop table TestTable;
Table dropped.
SQL>
SQL>

FLOOR(x): Returns the largest integer less than or equal to x

  
SQL> --FLOOR(x): Returns the largest integer less than or equal to x.
SQL>
SQL> select FLOOR(-5.2) from dual;
FLOOR(-5.2)
-----------
         -6
SQL>

round(345.678), ceil(345.678), floor(345.678)

  
SQL>
SQL> select round(345.678), ceil(345.678), floor(345.678)
  2  from   dual;
ROUND(345.678) CEIL(345.678) FLOOR(345.678)
-------------- ------------- --------------
           346           346            345

Use floor(), mod() and date calculation to show how many weeks and days an employee has been working here

  
SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal       NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;
Table created.
SQL> insert into emp values(1,"Tom","N",   "Coder", 13,date "1965-12-17",  800 , NULL,  20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20",  1600, 300,   30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" ,  "Tester",6,date "1962-02-22",  1250, 500,   30);
1 row created.
SQL> insert into emp values(4,"Jane","JM",  "Designer", 9,date "1967-04-02",  2975, NULL,  20);
1 row created.
SQL> insert into emp values(5,"Mary","P",  "Tester",6,date "1956-09-28",  1250, 1400,  30);
1 row created.
SQL> insert into emp values(6,"Black","R",   "Designer", 9,date "1963-11-01",  2850, NULL,  30);
1 row created.
SQL> insert into emp values(7,"Chris","AB",  "Designer", 9,date "1965-06-09",  2450, NULL,  10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(9,"Peter","CC",   "Designer",NULL,date "1952-11-17",  5000, NULL,  10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28",  1500, 0,     30);
1 row created.
SQL> insert into emp values(11,"Ana","AA",  "Coder", 8,date "1966-12-30",  1100, NULL,  20);
1 row created.
SQL> insert into emp values(12,"Jane","R",   "Manager",   6,date "1969-12-03",  800 , NULL,  30);
1 row created.
SQL> insert into emp values(13,"Fake","MG",   "Coder", 4,date "1959-02-13",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager",   7,date "1962-01-23",  1300, NULL,  10);
1 row created.
SQL>
SQL> select ename
  2  ,      floor(   (sysdate-bdate)/7) as weeks
  3  ,      floor(mod(sysdate-bdate,7)) as days
  4  from   emp
  5  where  deptno = 10;
ENAME         WEEKS       DAYS
-------- ---------- ----------
Chris          2315          5
Peter          2971          0
Mike           2491          6
SQL>
SQL> drop table emp;
Table dropped.