Database Reference
In-Depth Information
If you rely on a default date format and it changes, your application may be negatively affected. It might raise
an error back to the end user if the date cannot be converted, have a serious security flaw or, as bad, it might silently
insert
the wrong data.
Consider the follow
INSERT
statement, which relies on a default date mask:
Insert into t ( date_column ) values ( '01/02/03' );
Suppose the application was relying on a default date mask of
DD/MM/YY
to be in place. That would be February
1, 2003 (assuming that code was executed after the year 2000, but we'll visit the implications of that in a moment).
Now, say someone decides the correct date format should be
MM/DD/YY
. All of a sudden, that previous date changes to
January 2, 2003. Or someone decides
YY/MM/DD
is right, and now you have February 3, 2001. In short, without a date
format to accompany that date string, there are many ways to interpret it. That
INSERT
statement should be:
Insert into t ( date_column ) values ( to_date( '01/02/03', 'DD/MM/YY' ) );
And if you want my opinion, it has to be:
Insert into t ( date_column ) values ( to_date( '01/02/2003', 'DD/MM/YYYY' ) );
That is, it must use a four-character year. Several years ago, our industry learned the hard way how much time
and effort was spent remedying software that attempted to “save” 2 bytes. We seem to have lost that lesson over time.
There is no excuse nowadays
not
to use a four-character year! Just because the year 2000 has come and gone does not
mean you can now use a 2 character year. Think about birth dates, for example. If you enter a birth date using
Insert into t ( DOB ) values ( to_date( '01/02/10', 'DD/MM/YY' ) );
is that Feb 1st, 2010 or Feb 1st, 1910? Either one is a valid value; you cannot just pick one to be correct.
This same discussion applies to data leaving the database. If you execute
SELECT DATE_COLUMN FROM T
and fetch
that column into a string in your application, then you should apply an explicit date format to it. Whatever format
your application is expecting should be explicitly applied there. Otherwise, at some point in the future when someone
changes the default date format, your application may break or behave incorrectly.
Next, let's look at the datatypes themselves in more detail.
DATE Type
The
DATE
type is a fixed-width 7-byte date/time datatype. It will always contain the seven attributes of the century,
the year within the century, the month, the day of the month, the hour, the minute, and the second. Oracle uses an
internal format to represent that information, so it is not really storing 20, 05, 06, 25, 12, 01, 00 for June 25, 2005, at
12:01:00. Using the built-in
DUMP
function, we can see what Oracle really stores:
EODA@ORA12CR1> create table t ( x date );
Table created.
EODA@ORA12CR1> insert into t (x) values
2 ( to_date( '25-jun-2005 12:01:00',
3 'dd-mon-yyyy hh24:mi:ss' ) );
1 row created.
EODA@ORA12CR1> select x, dump(x,10) d from t;
X D
--------- -----------------------------------
25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1
