Date Function - Working with Dates

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

Example:

SELECT SYSDATE                                  * DUAL is a dummy table.
FROM DUL;

Result
----------------
30-MAY-13

Arithmetic with Dates

• Add or subtract a number to or from a date for a resultant date values.
• 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.

Example:

The example on the slide displays the last name and the number of weeks worked by the employee department number 10.

Date Functions

• MONTHS_BETWEEN(date1,date2): Finds the number of months between date1 and date2.
• ADD_MONTHS(date,n): Adds n number of calender months to date.
• NEXT_DAY(date,'char'): Finds the date of the next specified day of the week.
• LAST_DAY(date): Finds the date of the last day of the month that contains date.
• ROUND(date [, 'fmt']): Returns date rounded to the unit specified by the format model fmt.
• TRUNC(date [, 'fmt']): Returns date with the time portion of the day truncated to the unit specified by the format model fmt.

Using Date Functions

MONTHS_BETWEEN

 ADD_MONTHS

NEXT_DAY

LAST_DAY

ROUND

TRUNC