Data Management

Learn Oracle: Datatypes for SQL and PL/SQL, Dates

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:

    v_date_field DATE;

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.

Read the rest of this post


Editor's Picks