Func Part..2

FUNCTION CONTINUES

NUMBER FUNCTIONS::

1.ROUND()
2.TRUNC()
3.MOD()
4.POWER()

1.ROUND()::

The ROUND() Function mainly used for Round of the Decimal Values Or Round of the Values based on the Position Which is
Passed in the argument.

Example::

**Using Second Argument As Postitive Values--ROUND(Arg1,Positive Value)::

SELECT ROUND(324.5456,2) From Dual;
O/P--324.54
SELECT ROUND(324.5456,3) From Dual;
O/P--324.546
SELECT ROUND(324.5,1) From Dual;
O/P--325
SELECT ROUND(324.5,0) From Dual;
O/P--325
SELECT ROUND(340.5456) From Dual;
O/P--340

**Using Second Argument As Negative Values--ROUND(Arg1,Negative Values)::

SELECT ROUND(324.5456,-1) From Dual;
O/P--320
SELECT ROUND(324.5456,-2) From Dual;
O/P--300
SELECT ROUND(1324.5,-3) From Dual;
O/P--1000
SELECT ROUND(326.5,-1) From Dual;
O/P--330
SELECT ROUND(374.5456,-2) From Dual;
O/P--400
SELECT ROUND(2674.5456,-3) From Dual;
O/P--3000
TRUNC()::

The TRUNC() function mainly used for "Truncate" or "Remove" the Values(From Position) based on the Position
we mentioned in the second argument.

SELECT TRUNC(324.5456,2) From Dual;
O/P--324.54
SELECT TRUNC(324.5456,3) From Dual;
O/P--324.545
SELECT TRUNC(324.5,1) From Dual;
O/P--324.1
SELECT TRUNC(324.5,0) From Dual;
O/P--324
SELECT TRUNC(340.5456) From Dual;
O/P--340

MOD()::

The MOD() function mainly used for Divid the Argument1/Arg2 and Returns the remainter value of the Statement.

Example::

SELECT MOD(3,2) FROM dual;
O/P--1

POWER()::

The POWER() function mainly used for multiply the argumnet1 with arg2(Number Of Times) and ruturn the Output

SELECT POWER(3,4) FROM dual;
O/P--3*3*3*3====>81

DATE FUNCTION::


There are four DATE functions are there.
1.NONTHS_BETWEEN()
2.ADD_MONTHS()
3.NEXT_DAY()
4.LAST_DATE()

"SYSDATE" is an inbuilt Pseudo function ,It mainly used for Getting Current Server date.(01-Apr-16)

SELECT SYSDATE from dual:
O/P--01-Apr-16
SELECT SYSDATE+10 from dual;
O/P--10-Apr-16
SELECT SYSDATE-10 from dual;
O/P--20-Mar-16
*We can Perform Add ,Sub with "SYSDATE"

Condition::

1.Date + Number = Date
2.Date - Number = Date
3.Date - Date = Number
4.Date + Date = NOT POSSIBE

(DATE)-(DATE) Example::

We Can Subtract One date from another Date using
 TO_Date("Your Date","Format DD-Mon-YY")

Example::


SELECT TO_DATE('05-Apr-16','DD-Mon-YY') - TO_DATE('01-Apr-16','DD-Mon-YY') as "RESULT" FROM dual;
O/P--4

i)MONTHS_BETWEEN()::

The MONTHS_BETWEEN() is mainly used for return the number of months between from Month(Arg1) to till Month(Arg2)

SELECT MONTH_BETWEEN(SYSDATE,'01-Jun-15') FROM dual;
SELECT ROUND(MONTHS_BETWEEN(SYSDATE,'01-Jun-15')) FROM dual;
O/P--10

ii)ADD_MONTHS()

The ADD_MONTHS() increment the the Month(Arg1,Increment Month(+ Or -)

SELECT ADD_MONTHS(SYSDATE,3) FROM dual;
O/P--05-JUL-16
SELECT ADD_MONTHS(SYSDATE,-7) FROM dual;
O/P--05-SEP-15

NEXT_DAY()::

The NEXT_DAY() function is used for return the next date From(Argument 1).

SELECT NEXT_DAY(SYSDATE,'Thursday') FROM Dual;
O/P --NEXT Thursday Date--->14-Apr-16
SELECT NEXT_DAY(NEXT_DAY(SYSDATE,'FRIDAY'),'FRIDAY') FROM dual;
O/P ---Next 2nd Friday----->15-Apr-16

LAST_DAY()::

The LAST_DAY() function is used for returning the last day (Date) of the Month(Passed in the query)

SELECT LAST_DAY(SYSDATE) from dual;
O/P-->30-Apr-16

TYPE FUNCTIONS::

There are following three types types function are there,
1.TO_NUMBER()
2.TO_DATE()
3.TO_CHAR()


1.TO_NUMBER()::

TO_NUMBER() function is mainly used for converts a Character String into Number.

SELECT 5 + '5' FROM dual;--------Its an Implicit Conversion
O/P----10
SELECT 5 + TO_NUMBER('5') From dual;
O/P--->10

2.TO_DATE()::

The TO_DATE() function is mainly used for Converting the char & Other Unknown date format into a Oracle Standard DATE format.

For example--21-January-95 its a date format but oracle does not recognize the format, So in this case we can used TO_DATE()

SELECT TO_DATE('21-January-95','DD-Month-YY') From dual;
O/P---21-JAN-95

3.TO_CHAR()::

The TO_CHAR() function is mainly used for convert and displaying the Data base date format into used needed format.

SELECT TO_CHAR(SYSDATE,'DD Month Year') from dual;
O/P--->07 April Twenty Sixteen

3)i) Using ROMAN CHARACTER::

SELECT TO_CHAR(8,'RM') FROM dual;
O/P---viii

Example::
SELECT 'Due Date is' || TO_CHAR(SYSDATE,'DDspth month year') FROM dual;

O/P---Due Date is SEVENTH april twenty sixteen