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 :