Number Functions

ROUND :

• Rounds value to specified decimal

TRUNC :

• Truncates value to specified decimal

MOD :

• Returns remainder of division

ROUND Function

• ROUND function rounds the column, expression, or value to n decimal places. If the second argument is 0 or is missing, the value is rounded to zero decimal places. If the second argument is 2, the value is rounded to two decimal places. Conversely, if the second argument is –2, the value is rounded to two decimal places to the left (rounded to the nearest unit of 10).
• The ROUND function can also be used with date functions.

Using the ROUND Function :

• SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1);
• 45.92 46 50
  

TRUNC Function :

• TRUNC function truncates the column, expression, or value to n decimal places.
• TRUNC function works with arguments similar to those of the ROUND function. If the second argument is 0 or is missing, the value is truncated to zero decimal places. If the second argument is 2, the value is truncated to two decimal places. Conversely, if the second argument is –2, the value is truncated to two decimal places to the left. If the second argument is –1, the value is truncated to one decimal place to the left.
• Like the ROUND function, the TRUNC function can be used with date functions.

Using the TRUNC Function :

• SELECT TRUNC(45.923,2), TRUNC(45.923,0),TRUNC(45.923,-1);
• 45.92 45 50

Using the MOD Function :

• The MOD function finds the remainder of the first argument divided by the second argument.
  
• For all employees with job title of Sales Representative, calculate the remainder of the salary after it is divided by 5,000.
• SELECT ename, sal, MOD(sal, 5000) FROM emp WHERE job = 'SALESMAN';
• The above example calculates the remainder of the salary after dividing it by 5,000 for all employees whose job ID is SALESMAN
  

Working with Dates and Date Functions

CURRENT_DATE Function :

• _DATE is a date function that returns the current database server date and time.
  

You can use :

• CURRENT_DATE just as you would use any other column name.
• For example, you can display the current date by selecting CURRENT_DATE from a table.

Example :
Display the current date using the DUAL table.
» SELECT CURRENT_DATE;

Output
» date
» "2009-07-15"

Arithmetic with Dates :

• Add or subtract a number to or from a date for a resultant date value
• Subtract two dates to find the number of days between those dates
• Add hours to a date by dividing the number of hours by 24

Using Arithmetic Operators with Dates :

• SELECT ename, (CURRENT_DATE-hiredate)/7 AS WEEKS FROM emp WHERE deptno = 20;
• The above example displays the last name and the number of weeks employed for all employees in department 90. It subtracts the date on which the employee was hired from the current date (CURRENT_DATE) and divides the result by 7 to calculate the number of weeks that a worker has been
  employed.

Date Functions :
All date functions return a value of DATE data type:

• age(timestamp, timestamp) : Subtract arguments, producing a “symbolic” result that uses years and Months
• date_part(text, timestamp) : Get subfield (equivalent to extract);
• extract(field from timestamp) : The extract function retrieves sub fields such as year or hour from date/timevalues. source must be a value expression of type timestamp, time, or interval.
• date_trunc(’field’, source) : Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.

Using Date Functions :