The Oracle
database stores dates in an internal numeric format, representing the century,
year, month, day, hours, minutes, and seconds.
The default
display and input format for any date is DD-MON-RR. Valid Oracle dates are
between January 1, 4712 B.C., and December 31, 9999 A.D.
RR Date
Format
The RR date
format is similar to the YY element, but you can use it to specify different
centuries. Use the RR date format element instead of YY so that the century of
the return value varies according to the specified two-digit year and the last
two digits of the current year.
Oracle
Date Format
This data
is stored internally as follows:
CENTURY YEAR MONTH DAY HOUR
MINUTE SECOND
19 87
06 17 17 10 43
Centuries
and the Year 2000
When a
record with a date column is inserted into a table, the century
information is picked up from the SYSDATE function. However, when the date
column is displayed on the screen, the century component is not displayed (by
default).
The DATE
data type always stores year information as a four-digit number internally: two
digits for the century and two digits for the year. For example, the Oracle
database stores the year as 1987 or 2004, and not just as 87 or 04.
Using
the SYSDATE Function
SYSDATE is
a date function that returns the current database server date and time. You can
use SYSDATE just as you would use any other column name. For example, you can
display the current date by selecting SYSDATE from a table. It is customary to
select SYSDATE from a dummy table called DUAL.
Note: SYSDATE returns the current date
and time set for the operating system on which the database resides. Hence, if
you are in a place in Australia and connected to a remote database in a
location in the United States (US), sysdate function will return the US date
and time. In that case, you can use the CURRENT_DATE function that returns the
current date in the session time zone.
Arithmetic
with Dates
Because the
database stores dates as numbers, you can perform calculations using arithmetic
operators such as addition and subtraction. You can add and subtract number
constants as well as dates.
You can
perform the following operations:
•
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
last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;
The 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 (SYSDATE) and divides the result by 7 to calculate the number of
weeks that a worker has been employed.
Note: SYSDATE is a SQL function that
returns the current date and time. Your results may differ depending on the
date and time set for the operating system of your local database when you run
the SQL query.
If a more
current date is subtracted from an older date, the difference is a negative
number.
Date-Manipulation
Functions
Date
functions operate on Oracle dates. All date functions return a value of the
DATE data type except MONTHS_BETWEEN, which returns a numeric value.
• MONTHS_BETWEEN(date1, date2):
Finds the number of months between date1 and date2. The result
can be positive or negative. If date1 is later than date2, the
result is positive; if date1 is earlier than date2, the result is
negative. The noninteger part of the result represents a portion of the month.
• ADD_MONTHS(date, n): Adds n
number of calendar months to date. The value of n must be an
integer and can be negative.
• NEXT_DAY(date, 'char'):
Finds the date of the next specified day of the week ('char') following date.
The value of char may be a number representing a day or a character
string.
• LAST_DAY(date): Finds the
date of the last day of the month that contains date
The above
list is a subset of the available date functions. ROUND and TRUNC number
functions can also be used to manipulate the date values as shown below:
• ROUND(date[,'fmt']):
Returns date rounded to the unit that is specified by the format
model fmt. If the format model fmt is omitted, date is
rounded to the nearest day.
• TRUNC(date[, 'fmt']):
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.
For
example, display the employee number, hire date, number of months employed,
six-month review date, first Friday after hire date, and the last day of the
hire month for all employees who have been employed for fewer than 100 months.
SELECT employee_id,
hire_date,
MONTHS_BETWEEN
(SYSDATE, hire_date) TENURE,
ADD_MONTHS
(hire_date, 6) REVIEW,
NEXT_DAY
(hire_date, 'FRIDAY'), LAST_DAY(hire_date)
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 100;
Using
ROUND and TRUNC Functions with Dates
The ROUND
and TRUNC functions can be used for number and date values. When used with
dates, these functions round or truncate to the specified format model.
Therefore, you can round dates to the nearest year or month. If the format
model is month, dates 1-15 result in the first day of the current month. Dates
16-31 result in the first day of the next month. If the format model is year,
months 1-6 result in January 1 of the current year. Months 7-12 result in
January 1 of the next year.
Example:
Compare the
hire dates for all employees who started in 1997. Display the employee number,
hire date, and starting month using the ROUND and TRUNC functions.
SELECT employee_id,
hire_date,
ROUND(hire_date, 'MONTH'), TRUNC(hire_date, 'MONTH')
FROM employees
WHERE hire_date LIKE '%97';
