The following table lists the fields and their possible values for datetimes and intervals.
The way the Oracle database handles datetime values is pretty straightforward, but it seems to confuse many client-side and PL/SQL developers alike.
Oracle uses a 7 byte binary date format which allows Julian dates to be stored within the range of 01-Jan-4712 BC to 31-Dec-9999 AD.
SQL*Plus has converted the internal representation of the date into a nice string for us, but it has left out the time component. Because it has used the format mask specified by the parameter to decide how to implicitly convert the date to a string.
You can display the current database, instance and session NLS parameter values using this script.
To remedy this, we must either explicitly use the types in more detail.
The vast majority of problems people encounter are because of a misunderstanding about how dates are stored in the database.What you see on screen from a query is what's in the database right? Client tools, like SQL*Plus, convert datetime column values into something much nicer to look at.Both datetime and interval data types consist of fields.The values of these fields determine the value of the datatype.
To get the full data we have to either explicitly ask for it using the SELECT TO_CHAR(col1, 'DD-MON-YYYY HH24: MI: SS') AS col1, TO_CHAR(col2, 'DD-MON-YYYY HH24: MI: SS') AS col2 FROM t1; COL1 COL2 -------------------- -------------------- 27-APR-2013 27-APR-2013 1 row selected.SQL That string looks perfectly acceptable to me, because I understand the variations in date formats and that looks like a UK representation of "27th April 2013" to me, but the database doesn't know that.