Lesson 6 | Date/time functions for date manipulation |
Objective | Identify valid date-related functions and Oracle's standard date format. |
Oracle Date and Time Functions
This lesson looks at a few of the more commonly used date functions that work with the date portion of the date datatype. Since the date datatype also contains time, the next lesson illustrates functions you can use to manipulate the time portion of a date.
Date functions
The default display format for dates in Oracle is: DD-MON-YY. However, dates are actually stored in the database as a large number that facilitates date comparisons and date arithmetic. There are many functions for manipulating dates. The following table lists them all.
Function | Syntax | Description |
ADD_MONTHS | ADD_MONTHS(col1, n) | Add n months to col1 |
LAST_DAY | LAST_DAY(col1) | Date of the last day of the month for col1 |
MONTHS_BETWEEN | MONTHS_BETWEEN(col1, col2) | Number of months between two dates. Returns fraction of month if days of the two dates are different. |
NEW_TIME | NEW_TIME(col1, zone1, zone2) | Converts date and time from zone1 to zone2 |
NEXT_DAY | NEXT_DAY(col1, day) | Returns the date of the next day named by day (such as 'MONDAY') |
ROUND | ROUND(col1,[round_to]) | Returns the date rounded to the nearest day by default; can round to nearest month, quarter, year, hour, and so on based on round_to format |
TRUNC | TRUNC(col1, [trunc_to]) | Returns the date with the time removed; can truncate to nearest month, quarter, year, hour, and so on based on trunc_to format |
TO_CHAR | TO_CHAR(col1, date_format) | Returns date and/or time based on format listed |
TO_CHAR
You can display an Oracle date as any of dozens of format combinations. Just use this basic layout: TO_CHAR(col1,'format')
The format parameter is always enclosed in single quotes and uses symbols defining how the date is displayed. Here are some of the date format symbols:
There are many more, but these are the major ones.
The following Slide Show shows several examples of the TO_CHAR()
function with a variety of date formats, plus some date arithmetic.
- This example shows the day and month spelled out and the year displayed as a four-digit year.
- You can add or subtract days to a date with a plus sign or minus sign
- You can find the difference between two dates by subtracting them. When the difference is calculated, the time stored with each date is taken into account.
- You can also use the functions, such as NEXT_DAY, to calculate dates.
Date Formats Date Arithmetic
Y2K and the Oracle date