Getting the System Dates and Time
Retrieving Data with Queries
2-20 Oracle Database 2 Day Developer's Guide
ADD_MONTHS(hire_date, 6) "New Date"
FROM employees;
The results of the query appear.
Name Date Started New Date
------------------------ ------------------------- -------------------------
Steven King 17-JUN-87 17-DEC-87
Neena Kochhar 21-SEP-89 21-MAR-90
Lex De Haan 13-JAN-93 13-JUL-93
...
107 rows selected
In
, you will use the
SYSTIMESTAMP
function determine the current
system time and date.
SYSTIMESTAMP
is similar to
SYSDATE
, but also contains time of
day information, including the time zone and fractional seconds. See Oracle Database
SQL Language Reference for information about the
SYSTIMESTAMP
function.
Note that instead of an
hr
schema table, you are using the table
DUAL
, a small table in
the data dictionary that you can reference to guarantee a known result. See Oracle
Database Concepts for information about the
DUAL
table and Oracle Database SQL
Language Reference for information about selecting from the
DUAL
table.
Example 230 Getting the System Date and Time
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) || ':' ||
EXTRACT(MINUTE FROM SYSTIMESTAMP) || ':' ||
ROUND(EXTRACT(SECOND FROM SYSTIMESTAMP), 0) || ', ' ||
EXTRACT(MONTH FROM SYSTIMESTAMP) || '/' ||
EXTRACT(DAY FROM SYSTIMESTAMP) || '/' ||
EXTRACT(YEAR FROM SYSTIMESTAMP) "System Time and Date"
FROM DUAL;
The results of the query appear.
System Time and Date
------------------------------------------------------------
18:25:56, 4/5/2007
Your result would change, depending on the current
SYSTIMESTAMP
value.
Using Data Type Conversion Functions
Oracle Database has data functions for converting between different data types. This is
particularly useful when you need to display data of different data types in the same
column.
There are three general types of conversion functions: for characters (
TO_CHAR
), for
numbers (
TO_NUMBER
), for dates (
TO_DATE
) and for timestamps (
TO_TIMESTAMP
).
You will use the
TO_CHAR
function to convert a date into a desired format.
HIRE_DATE
values to a '
FMMonth DD YYYY
' format; note
that the FM option removes all leading or trailing blanks from the month name. Other
options you could use include '
DD-MON-YYYY AD
', '
MM-DD-YYYY HH24:MI:SS
', and
so on.
See Also:
Oracle Database SQL Language Reference for datetime functions
