Browse DevX
Sign up for e-mail newsletters from DevX


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

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.

DB2 and Oracle provide rich sets of facilities for manipulating date and time data types. Both provide native support for these data types, which eliminates the need to transform them into other types in order to support arithmetic operations like addition and subtraction. As with storage, both differ in date arithmetic operations as well.

Date Arithmetic in DB2

DB2 provides support for three date arithmetic operations:
  • Subtracting DATE, TIME, or TIMESTAMP value from corresponding types to yield DURATION type (e.g., two DATE values can be subtracted, yielding the interval between them as DATE DURATION.)
  • Addition and subtraction of similar DURATIONS from DATE, TIME, and TIMESTAMP (e.g., time duration can be added or subtracted from TIME or TIMESTAMP values only.)
  • Addition and subtraction of labeled durations (of compatible time) from DATE, TIME, and TIMESTAMP (e.g., labeled duration of MICROSECONDS can be added or subtracted from TIMESTAMP values only.)

DURATION values are available only during execution time and are represented as packed decimal digits. In contrast to DB2 date and time types, duration carries the sign information to represent positive and negative duration. For example, SELECT DATE ('2005-03-01') - DATE ('2005-02-01') FROM SYSIBM.SYSDUMMY1 gives 00000100 as duration, which means a difference of one month between the two date values.

In DB2, date arithmetic is similar to numerical arithmetic using packed decimal digits. In the above example, DB2 would subtract the packed decimal representation of operand 1 from the packed decimal representation of operand 2. (Click here for more information on SYSIBM.SYSDUMMY1.)

Be cautious when you add or subtract two durations as it may lead to obvious wrong results. For example, if you add a duration of 1 day to a duration of 99 days, DB2 will return 1 month as the duration instead of 100 days—because DB2 does a packed decimal addition rather than a date addition.

Suppose you need to find the duration between two date or timestamp values such as days, months, or years. In such cases, first convert each operand into days or months or years using DB2 functions like DAYS or MONTH or YEAR, and then perform the arithmetic operation. For example, SELECT DAYS (DATE ('2005-03-01')) - DAYS (DATE ('2005-02-01')) FROM SYSIBM.SYSDUMMY1 returns the duration as days between two dates. SELECT MONTH (DATE ('2005-03-01')) - MONTH (DATE ('2005-02-01')) FROM SYSIBM.SYSDUMMY1 returns the duration as number of months between two dates. SELECT YEAR(DATE('2005-03-01')) - YEAR(DATE('2005-02-01')) FROM SYSIBM.SYSDUMMY1 returns the duration as number of years between two dates.

To add a day, month, or year to a date or timestamp value, you can use labeled types provided by DB2. For example, SELECT DATE('2005-04-01') + 10 DAYS FROM SYSIBM.SYSDUMMY1 adds 10 days to the given date. SELECT DATE('2005-04-01') + 2 MONTHS FROM SYSIBM.SYSDUMMY1 adds two months to the given date. SELECT DATE('2005-04-01') + 1 YEAR FROM SYSIBM.SYSDUMMY1 adds a year to the given date value. Labeled types are easy to use, they are self-documenting, and they make the code more readable.

Date Arithmetic in Oracle

Like DB2, Oracle can perform three date arithmetic operations:
  • Subtract two DATE values, yielding the interval between the two as days
  • Add an interval to a DATE or TIMESTAMP value (For example, adding six months to the current date yields a DATE value of six months past the current date.)
  • Add or subtract two interval values, yielding an INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND data type.

In contrast to DB2, which returns the interval between two DATE values as a duration type, Oracle returns the interval between two date values as days. For example, SELECT TO_DATE('01-MAR-04') - TO_DATE('01-FEB-04') from DUAL returns 29 days rather than 0000 Years, 01 months, and 00 days as it would in DB2. (Click here for more information on the DUAL table in Oracle.)

Oracle doesn't have functions for DAYS, MONTH, and YEAR like DB2 does. In Oracle, the intervals between two DATE and TIMESTAMP values are always returned as days. To get the interval in months, it provides a built-in function called MONTHS_BETWEEN. For example, SELECT MONTHS_BETWEEN (TO_DATE('01-DEC-04'),TO_DATE('01-FEB-04') ) FROM DUAL returns 10 months. If the second argument is later than the first, the result will be a negative value.

Similar to labeled types in DB2, if you want to add 'X' months to the current date or timestamp, first convert 'X' into an INTERVAL YEAR TO MONTH data type or INTERVAL DAY TO SECOND data type, and then add the intervals to date as in the following example:


Instead of using the built-in functions to convert a numeric value to an interval type, you can declare an interval type explicitly as INTERVAL 'X' DAY or INTERVAL 'X' MONTH. For example, SELECT SYSDATE + INTERVAL '25' DAY FROM DUAL adds 25 days to the current date. Similarly SELECT SYSDATE + INTERVAL '2' MONTH FROM DUAL adds two months to the current date.

Why You Need to Know

You have seen the basic differences between date and time data types in DB2 and Oracle. A clear understanding of these differences will equip you to overcome most of the challenges related to migrating data from one database system to another—especially those related to chronological information. However, do not overlook other differences between the two, like formatting, time zone information, etc., which are also critical in real world scenarios.

Shibu Kalluvila Raj is an associate consultant for US Technology Resources LLC, a global information technology and BPO service provider. He is also a graduate research student in computer science at DePaul University in Chicago, studying database systems implementation, software architecture, and agile development.
Thanks for your registration, follow us on our social networks to keep up-to-date