Dates in Oracle are stored in numeric format.
Each date represents the century, year, month, day, hours, minutes, and seconds.
The default date format is DD-MON-YY.
Using the SYSDATE function, you can display the current date and time.
You can use SYSDATE as you would use any other column name.
In order to view the time you will need to use the TO_CHAR function along with a
format mask which includes a time format element.
For example, you can display the current date by selecting SYSDATE from a table.
You usually select SYSDATE from a dummy table called DUAL.
All users can access the DUAL table.
It is owned by the SYS user.
It consists of one column known as DUMMY, and one row with the value X.
When you want to return a value – not necessarily a date – once only, the DUAL table
is useful.
To display the current date using the DUAL table, you use the command shown.
And the output displays the current date.
Dates are stored as numbers in the database, which enables you to perform arithmetical
calculations on them.
You can add or subtract a number to or from a date for a resultant date value.
You can subtract one date from another to find the number of days between them.
You can also add hours to a date and time value.
This is performed by dividing the number of hours in the time value by 24.
Let’s take a look at how you can manipulate dates.
Let’s say that you want to add and subtract days from the current date.
To add one hundred days to the current date, you type the command shown.
The result is displayed in the column SYSDATE+100.
If you want to subtract 34 days from the current date, you type the command shown.
The result is displayed in the column SYSDATE-34.
Let’s say you want to subtract the hire date from the current date – you type the
command shown.
The result shows the number of days between the current date and the hire date.
Let’s say you want to display dates in terms of weeks.
To show the number of weeks each employee in department 10 has worked, you first
subtract the hiredate from the current date.
Then you divide the result by seven.
You can use date functions in a similar way to number functions to operate on Oracle
dates.
The MONTHS_BETWEEN function finds the number of months between two dates.
The result is displayed as a numeric value.
This is the only date function that does not return a date value.
The syntax for MONTHS_BETWEEN is shown – date1 and date2 are the dates that you
want to find the number of months between.
The result displayed can be either positive or negative.
If date1 is later than date2, the result is positive.
And if date1 is earlier than date2, the result is negative.
The ADD_MONTHS function adds to a particular date the number of calendar months
you specify.
You need to supply a whole number for the number of months you want to add.
You can specify a negative number.
The syntax for ADD_MONTHS is shown.
The n in the syntax is the number of calendar months that you want to add to the
date.
The value for n must be an integer, and it may be negative.
The NEXT_DAY function identifies the date of the next specified day of the week.
The syntax is as shown here.
Char in the syntax may be a number representing a day, or a character string.
The days are numbered one to seven, starting with Sunday.
The LAST_DAY function identifies the date of the last day of the specified month.
The syntax for LAST_DAY is shown.
The ROUND date function returns the date rounded to the nearest date of the unit
you specify.
For example the month or year.
If you do not specify a unit, the date is rounded to the nearest day.
The syntax for ROUND is shown.
The fmt in the syntax is used to represent the format you want to round the date
to.
The TRUNC function returns the date, truncated to the nearest date of the unit you
specify.
If you do not specify a unit, the date is truncated to the nearest day.
The syntax for TRUNC is shown here.
The ROUND and TRUNC date functions operate in a similar manner to their number function
namesakes.
Let’s take a look at some examples of date functions.
Suppose employees are reviewed annually, based on their hiredate.
You can use date functions to calculate the next review date for employees.
To simplify the SELECT statements to follow, let’s create a substitution variable
review that calculates the review date.
For more information on substitution variables, see the CBT Systems course “Oracle
Introduction: Subqueries, Reports, and DML”.
Let’s say you want to find the first Friday after the review date.
You use the NEXT_DAY function to do this.
And you can display the last day of the month in which the review date occurs using
the LAST_DAY date function.
Let’s say you want to search for the date six months after the review date.
You use the ADD_MONTHS function to do this.
And you can use the MONTHS_BETWEEN function to find the number of months between
today and the review date for this year.
If the review date is later than today, the value is negative.
If it’s earlier, it’s positive.
If the current date is 17-Jun-98, and you want to round the current date to the
nearest month, you can use this command.
And to truncate the current date you can use this command.
Here you can compare the effects of TRUNC and ROUND with the current date.