NEXT_DAY(date, ‘day’ ) :  It gives date of the next occurrence of the specified day after the given date.
 

SQL> SELECT NEXT_DAY('01-JAN-05','FRI') FROM DUAL;

NEXT_DAY(                                 (Next Friday after 1-jan-05 is on 7-jan-05)
---------
07-JAN-05

TO_DATE (string): This function converts a string into an Oracle date.

SQL> SELECT TO_DATE('01 JANUARY 2005','DD MONTH YYYY') FROM DUAL;

TO_DATE('
---------
01-JAN-05

SQL> SELECT TO_DATE('MAR 05 01','MON YY DD') FROM DUAL;

TO_DATE('
---------
01-MAR-05

SQL> SELECT TO_DATE('01/01/05', 'DD/MM/YY') FROM DUAL;

TO_DATE('
---------
01-JAN-05

The USER, SYSDATE Functions:
 
          USER   function displays login name of the user.

SQL> SELECT USER FROM DUAL;

USER
------------------------------
SCOTT

SYSDATE  function displays system date.(Date in your windows)

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
1-JAN-05

CONVERSION FUNCTIONS:

TO_CHAR: This function is used to convert a date or number to character string.
SQL>  SELECT TO_CHAR(SYSDATE,'DAY DD MONTH YYYY') FROM DUAL

TO_CHAR (SYSDATE,'DAYDDMONTH
---------------------------
SATURDAY 01 JANUARY   2005

SQL> SELECT TO_CHAR (SYSDATE,' DD DY MM YY') FROM DUAL;

TO_CHAR (SYSDA
-------------
 01 SAT 01 05

NVL()  Function: This function is used to substitute any null value with a user-defined value.

Consider the following data from EMP table of SCOTT.

SQL> SELECT EMPNO, ENAME, SAL, COMM FROM EMP;

     EMPNO  ENAME             SAL       COMM
---------- ---------- ---------- -------------------------
      7369  SMITH               800
      7499  ALLEN            1600            300
      7521  WARD             1250            500
      7566  JONES            2975
      7654  MARTIN          1250          1400
      7698  BLAKE           2850
      7782  CLARK           2450
      7788  SCOTT            3000
      7839  KING                5000
      7844  TURNER          1500                 0
      7876  ADAMS           1100
      7900  JAMES             950
      7902  FORD              3000
      7934  MILLER            1300

In the above table except for 7499, 7521, 7654 and 7844 all others commissions are null
To display their commission as “0” (zero)
We can use NVL() function as shown below.

SQL> SELECT EMPNO, ENAME, SAL, NVL (COMM, 100) FROM EMP

     EMPNO  ENAME             SAL   NVL(COMM,100)
      ---------- ---------- ---------- -------------
      7369  SMITH             800         100
      7499  ALLEN            1600         300
      7521  WARD             1250         500
      7566  JONES            2975         100
      7654  MARTIN           1250        1400
      7698  BLAKE            2850         100
      7782  CLARK            2450         100
      7788  SCOTT            3000         100
      7839  KING             5000         100
      7844  TURNER           1500         0
      7876  ADAMS            1100         100
      7900  JAMES             950         100
      7902  FORD             3000         100
      7934  MILLER           1300         100

SQL> SELECT EMPNO, ENAME, SAL, NVL (COMM, 888) FROM EMP

     EMPNO  ENAME         SAL  NVL(COMM,888)
---------- ---------- ---------- -------------
      7369  SMITH          800           888
      7499  ALLEN         1600           300
      7521  WARD          1250           500
      7566  JONES         2975           888
      7654  MARTIN        1250          1400
      7698  BLAKE         2850           888
      7782  CLARK         2450           888
      7788  SCOTT         3000           888
      7839  KING          5000           888
      7844  TURNER        1500             0
      7876 ADAMS         1100           888
      7900  JAMES          950           888
      7902  FORD          3000           888
      7934  MILLER        1300           888

In above queries we have seen how to substitute a value when the comm is null.
If one want to display "Commission not payed" against the employees who have no
commission we can write the following query.

SQL > SELECT ENAME, SAL, NVL(TO_CHAR(COMM),'Commission Not Payed') FROM EMP;

ENAME             SAL  NVL(TO_CHAR(COMM),'COMMISSIONNOTPAYED')
---------- ---------- ----------------------------------------
SMITH             800  Commission Not Payed
ALLEN            1600  300
WARD             1250  500
JONES            2975  Commission Not Payed
MARTIN           1250  1400
BLAKE            2850  Commission Not Payed
CLARK            2450  Commission Not Payed
SCOTT            3000  Commission Not Payed
KING             5000  Commission Not Payed
TURNER           1500  0
ADAMS            1100  Commission Not Payed
JAMES             950  Commission Not Payed
FORD             3000  Commission Not Payed
MILLER           1300  Commission Not Payed

Next Page: Tutorial 4: SQL functions - Page 5