Overview

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html#overview

Oracle supports both date and time, albeit differently from the SQL2 standard. Rather than using two separate entities, date and time, Oracle only uses one, DATE. The DATE type is stored in a special internal format that includes not just the month, day, and year, but also

the hour, minute, and second.

The DATE type is used in the same way as other built-in types such as INT. For example, the following SQL statement creates a relation with an attribute of type DATE:

create table x(a int, b date);

DATE Format

When a DATE value is displayed, Oracle must first convert that value from the special internal format to a printable string. The conversion is done by a function TO_CHAR, according to a DATE format. Oracle’s default format for DATE is “DD-MON-YY“. Therefore, when you issue the query

select b from x;

you will see something like:

B
---------
01-APR-98

Whenever a DATE value is displayed, Oracle will call TO_CHAR automatically with the default DATE format. However, you may override the default behavior by calling TO_CHAR explicitly with your own DATEformat. For example,

SELECT TO_CHAR(b, 'YYYY/MM/DD') AS b
FROM x;

returns the result:

B
---------------------------------------------------------------------------
1998/04/01

The general usage of TO_CHAR is:

TO_CHAR(<date>, '<format>')

where the <format> string can be formed from over 40 options. Some of the more popular ones include:

, for example.