Use the article index for more articles on this subject.
10g provides three types of date related datatypes: date, timestamps
and intervals. Today I will be talking about all three of these.
DATE datatype is a datetime datatype. It stores a date and a time. The
date portion is based on the number of days since January 1, 4712 BC.
The time portion is based on the number of seconds since mid-night.
A date field is declared as a variable name followed by the DATE keyword:
There is no “correct” format for a date. The format is determined by several variables. At the database
level, you can set the NLS_DATE_FORMAT. Regardless of how the default
is set in your database, you should always make sure you explicitly use
a date format mask when converting.
I will cover conversion
functions for all data types in a future article but I think this is
If you need to compare a date to a literal, make sure to use
a date format mask. You could say:
WHERE date_field = ’01-01-1999′
or you could say:
WHERE date_field = to_date(’01-01-1999′)
would consider both of those bad programming practices.
The first is
allowing an implicit conversion between character and date and both of
them are assuming some kind of default date format. A better practice
would be to use:
WHERE date_field = to_date(’01-01-1999′, ‘DD-MM-YYYY’)
that leads us to why we use a DATE instead of a character field or a
numeric. I’m often asked about best practices with dates and times. For
some reason I have never understood, some people feel that storing
dates and times as characters and numbers is better than allowing
Oracle to handle that data in a native format. Call me lazy, but if
Oracle can do it for me, I am going to let it.