This article describes how IBM implemented the DATE data type for DB2 and discusses what calendar is used, what the JULIAN_DAY function really does, why date arithmetic is random, how to calculate birthdays, and how to find month ends using DB2.
DB2 stores a date using four bytes: two bytes for the year, one byte for the month, and one byte for the day. The storage is of course binary, so the so-called Y2K bug (which supposed that programs would require two bytes for two digits of a date) was never an issue affecting DB2 databases.
DB2's storage method differs from others. In Microsoft SQL Server, Oracle, and a few other DBMSs, a date is always a combined date and time value, although of course the time portion can be 00:00:00. By having a date data type that's truly separate from a time data type, DB2 is adhering more closely to standard SQL than others.
Range Of Values
Here is a chart showing the minimum and maximum date values according to the ANSI/ISO SQL Standard and three DBMS vendors.
MINIMUM DATE MAXIMUM DATE ------------- ------------- ANSI SQL 0001-01-01 9999-12-31 IBM DB2 0001-01-01 9999-12-31 Microsoft SQL Server 1753-01-01 9999-12-31 Oracle January 1 4713 BC 9999-12-31
DB2's range of possible dates corresponds exactly to the ANSI/ISO requirement. Its range is greater than SQL Server's range, but less than Oracle's range (Oracle allows dates since the beginning of the "Julian Day Number" period, which we'll discuss later).
Julian Or Gregorian Calendar?
In 1582 Pope Gregory XIII introduced the
Gregorian calendar to replace Julius Caesar's Julian calendar. Different countries switched from Julian to Gregorian reckoning at different times -- 1582 for Spain, 1752 for England and her colonies, 1918 for Russia, and so on. Gregory changed the Julian leap-year rule (which simply declared that every fourth year is a leap year) by adding a new rule: that every hundredth (but not every four-hundredth) year is not a leap year. Gregory also declared that some days must be removed from the calendar at the time of the switch.
That is, in the Julian calendar, there is a simple rule that says that every year that is divisible by four is a leap year. In our familiar Gregorian calendar, the rule is slightly more complex: Every year that is divisible by four is a leap year, except that every year that is (a) divisible by 100 and (b) indivisible by 400, is not a leap year. The effect of the Julian rule is that a 400-year period has (365 times 400 plus 100) 146100 days, while the effect of the Gregorian rule is that a 400-year period has (365 times 400 plus 100 minus 3) 146097 days.
This is what DB2 does:
- DB2 follows the Gregorian rule through the entire period from 0001-01-01 to 9999-12-31.
- DB2 does not drop any dates from the calendar for the year 1582.
Once again, DB2 is following the ANSI/ISO SQL Standard requirement in this respect. But many -- indeed, most -- computer programs operate differently. For example, Oracle considers "February 29 1300" to be a valid date (DB2 does not) and Oracle says the result of the date-arithmetic expression "October 15 1582 - October 4 1582" is one day (DB2 says the result is eleven days). Therefore any calculations involving old dates will introduce portability problems.
The JULIAN_DAY function
The DB2 manual says: "JULIAN_DAY: Returns an integer value representing the number of days from January 1, 4712 B.C. (the start of Julian date calendar) to the date value specified in the argument."
The quote shows an error in DB2's manual. DB2's JULIAN_DAY function actually returns the number of days since November 24 4714 BC (we are stating the date here according to the Gregorian calendar since that is what DB2 uses, after all).
Now let's compare DB2's Julian-day function with Oracle's. The chart below shows some AD calendar dates and the numbers that the DB2 and Oracle Julian-day functions return.
DATE ORACLE'S JULIAN DAY DB2'S JULIAN DAY DIFFERENCE ---- ------------------- ---------------- ---------- February 28 1970 2440646 2440646 0 ... October 16 1582 2299162 2299162 0 October 15 1582 2299161 2299161 0 October 4 1582 2299160 2299150 -10 October 3 1582 2299159 2299149 -10 ... February 28 1000 2086366 2086361 -5 ... February 28 600 1940266 1940264 -4
DB2 and Oracle are compatible if one uses dates after October 14 1582. Otherwise the values differ. If you could enter BC dates in DB2, you'd find that the difference can become quite large. To DB2, Julian Day 0 is November 24 4714 BC (proleptic Gregorian) instead of January 1 4713 BC (proleptic Julian).
To sum it up:
- DB2 follows the ANSI/ISO SQL Standard date requirements quite closely.
- DB2 uses the Gregorian calendar and is therefore out of step with some other products.
- DB2's manual contains an error in its description of the JULIAN_DAY function -- but the function does return correct values for dates greater than or equal to October 15 1582.
Arithmetic With Durations
DB2 allows addition and subtraction of dates and durations, where a duration is a DECIMAL(8,0) value with the format "yyyymmdd". For example, here's how to add a duration of 1 year, 2 months, and 3 days to date1:
date1 + 00010203
Durations are more flexible than the ANSI/ISO SQL INTERVAL data type, and generally we could say that date arithmetic is more intuitively obvious with DB2 than with any other SQL DBMS. However, three common problems exist.
The first problem is that, since there are only two digits to represent a number of days, what does one do if the result should be a number of days -- and there might be more than 99 days? The solution for addition is to use labelled durations, for example:
date1 + 100 DAYS
The solution for subtraction is to convert to Julian Day numbers, for example:
JULIAN_DAY(date1) - JULIAN_DAY(date2)
The second date arithmetic problem is: What should happen if the result is invalid because the day does not exist in the month? For example, what should the result be when you add 1 MONTH to MARCH 31? Clearly the answer cannot be APRIL 31, so DB2 truncates the result and returns APRIL 30. This is called "end of month adjustment" and it's what most DBMSs (including Ingres and Oracle) do. However, it is not the method that the SQL Standard requires. An ANSI/ISO-standard DBMS is supposed to return an error message indicating there is an error in the date arithmetic (because the result is an invalid date).
The third date arithmetic problem is predicting the result, since a duration is ambiguous. For example, suppose you want to add a single duration value of 1 month and one day to a date:
date1 + 00000101
Assume date1 is "June 30". The result of this expression then depends on the order that DB2 adds the duration's month and day components. If the month is added first, the result is: JUNE 30 + 1 MONTH = JULY 30 and JULY 30 + 1 DAY = JULY 31. If the day is added first, the result is: JUNE 30 + 1 DAY = JULY 1 and JULY 1 + 1 MONTH = AUGUST 1. So ... Question #1 is: Will DB2 return July 31 or August 1?
Even if the day component of the duration is zero, there is still a chance of ambiguity. For example, suppose you want to add a single duration value of 1 year and 1 month to a date:
date1 + 00010100
This time, assume that date1 is "January 29 1999". If DB2 adds the month first, the result is: JANUARY 29 1999 + 1 MONTH = FEBRUARY 28 1999 and FEBRUARY 28 1999 + 1 YEAR = FEBRUARY 28 2000. If the year is added first, the result is: JANUARY 29 1999 + 1 YEAR = JANUARY 29 1999 and JANUARY 29 1999 + 1 MONTH = FEBRUARY 29 1999. So ... Question #2 is: Will DB2 return February 28 1999 or February 29 1999?
As it happens, the DB2 manual says the order of evaluation is "years, then months, then days" for addition and "days, then months, then years" for subtraction. (We believe that DB2 treats the addition of a negative duration as a type of subtraction.) Therefore, results will be consistent. But DB2's choice is arbitrary -- so the answer to both Question #1 and Question #2 is: if you don't know, you shouldn't use multiple-component durations.
About the Authors
Peter Gulutzan (firstname.lastname@example.org) and Trudy Pelzer (email@example.com) have worked with SQL since 1986 and have written numerous articles and four books together. Their most recent effort, SQL Performance Tuning (Addison-Wesley 2002), discusses portability and optimization issues for DB2 and seven other DBMSs and has garnered rave reviews from eWeek magazine and sql-server-performance.com. The authors' publicity and errata site for the book are available here.
Brad M. writes: Subtraction works with labeled durations as well:
SELECT DATE('2003-01-01') + 100 DAYS FROM SYSIBM.SYSDUMMY1 SYSDUMMY1 ; +------------+ | | +------------+ 1_| 2003-04-11 | +------------+ SELECT DATE('2003-01-01') - 100 DAYS FROM SYSIBM.SYSDUMMY1 SYSDUMMY1 ; +------------+ | | +------------+ 1_| 2002-09-23 | +------------+
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free DB2 tips and scripts.
- Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
- Best Web Links: DB2 tips, tutorials, and scripts from around the Web.
This was first published in November 2005