Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Dates in DB2 and Oracle: Same Data Type, Different Behavior

Date, time, and their variants differ more than any other data types when you compare DB2 and Oracle. Find out more about these differences and why they're important to know.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

ost enterprise applications today use database systems to persist data, and they utilize these data for various organizational needs. Often these applications need to store, retrieve, and manipulate date and time values to perform various business processes. Commercial databases, such as IBM's DB2 Universal Database Ver. 8.2 (DB2) and Oracle 10g (Oracle), provide extensive native support for date and time data types, which reduces the load of manipulating these data types and increases performance. Being from different software houses, they provide different levels of support for these data types, as well as differing storage, retrieval, and manipulation functionalities.

This article provides an analysis of the basic differences between the date and time data types in DB2 and Oracle, focusing on DATE, TIME (available only in DB2), and TIMESTAMP in particular. It begins by introducing the data types in each database and moves on to cover their storage needs, internal representation, and values ranges. Finally, it discusses the differences in their date arithmetic operations. Understanding these differences can come in handy, especially when you are migrating data from one database to another.

Date and Time Types in DB2 and Oracle

DB2 provides three basic data types for working with date and time: DATE, TIME, and TIMESTAMP (see Table 1).

Data Type Example Constituent Elements Description
DATE 2005-01-01 Year, month, and day Represents January 1, 2005
TIME 13:00:00 Hour, minutes, and seconds Represents 1 PM
TIMESTAMP 2005-01- Year, month, day, hour, minute, second, and microseconds Represents January 1, 2005, 12:00 PM
Table 1. Date and Time Types in DB2

In DB2, TIMESTAMP is a combination of DATE and TIME values, with the time value having precision up to fractional microseconds (one-millionth of a second).

Oracle provides only two basic data types for working with date and time information: DATE and TIMESTAMP. (Oracle does offer TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE as well, but they are just variations of the TIMESTAMP type and are beyond the scope of this discussion.)

Oracle has no equivalent to DB2's TIME data type. Instead, it stores time with DATE values (see Table 2).

Data Type Example Constituent Elements Description
DATE 2005-01-01 12.00.00 Year, month, day, hour, minute, and seconds Represents January 1, 2005, 12:00 PM
TIMESTAMP 2005-01- Year, month, day, hour, minutes, seconds, and nanoseconds Represents January 1, 2005, 12:00 PM
Table 2. Date and Time Types in Oracle

In Oracle, the TIMESTAMP type is an extension of the DATE type, with the time value having precision up to nanoseconds (one-billionth of a second).

MIN and MAX Values

Table 3 presents the minimum and maximum values for DATE, TIME, and TIMESTAMP in DB2 and Oracle.

Data Type Minimum Value Maximum Value
DATE 0001-01-01 9999-12-31
TIME 00.00.00 24.00.00
TIMESTAMP 0001-01- 9999-12-
Data Type Minimum Value (excluding Year 0) Maximum Value (excluding Year 0)
DATE - 4712-01- 9999-12-31
TIMESTAMP - 4712-01- 9999-12-
Table 3. Minimum and Maximum Values for DB2 and Oracle

DB2 and Oracle differ in the minimum values they can store in date and time data types. DB2 stores DATE values starting from Common Era (CE) whereas Oracle starts from year -4712, which is Before Common Era (BCE).

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date