Search SSWUG.ORG:

ARTICLES




A Date with DB2

By Craig S. Mullins

One of the more frequent questions I get these days has to do with manipulating DATE data in DB2 tables. Either folks are looking to convert a column stored as a CHAR into a DB2 date, or they are looking to format a DB2 DATE column into a format other than those supported natively by DB2.

So, let’s start off by quickly examining the native DB2 formats for displaying DATE columns. There are four formats, as well as the ability to create a locally defined format, as shown in Table 1.

Name

Layout

Example

ISO

YYYY-MM-DD

2002-10-22

USA

MM/DD/YYYY

10/22/2002

EUR

DD.MM.YYYY

22.10.2002

JIS

YYYY-MM-DD

2002-10-22

LOCAL

Locally defined layout

N/A

Table 1: DB2 DATE formats.

If one of the native date layouts suffices, it is a simple matter to convert a DB2 DATE column into that format for display. Simply use the built-in column function CHAR yo convert the column into any of the formats in Table 1. To display the date as MM/DD/YYYY you would use the USA date format. For example, to return a column named START_DATE in USA format you can execute the function as follows:

CHAR(START_DATE,USA)

The first argument is the column name and the second argument is the format.

More Complicated Translations

But what if the desired format is not one of the native formats? And it is not worth the effort to code a LOCAL format? Well, in those cases you can usually apply a series of DB2 functions to manipulate a date into a desired format.

For example, what if you want to display a date as MMDDYYYY, without any separating characters. A quick glance of Table 1 shows that this is not a supported format. To accomplish this, though, you can rip the date apart using functions and then put it back together again as character output. If the date column in question is HIRE_DATE, for example, try something like this:

SELECT STRIP(CHAR(MONTH(HIRE_DATE))) ||
                    STRIP(CHAR(DAY(HIRE_DATE))) ||
                    STRIP(CHAR(YEAR(HIRE_DATE)))
FROMTABLE
WHERE...

All of these functions are required for the following reasons:

The MONTH, DAY, and YEAR functions return the month, day, and year component of the HIRE_DATE, each as a large integer.

The CHAR function converts the integer to a string representation. It will have trailing blanks.

The STRIP function removes the trailing blanks.

The concatenation function (that is, ||) combines the results of each into a single "field".

Of course, you can use other variations of this theme to reformat your dates into many different display formats (such as, using SUBSTR to display two-digit years).

Non-DB2 Dates

Well, the above information is all well-and-good, but what if you have eschewed the DB2 DATE data types and stored dates in a CHAR column? For example, perhaps you have a date stored in a CHAR(8) column formatted as YYYYMMDD (with no dashes or slashes) and you want to compare it to a DB2 date?

First of all, this is a bad idea. Date data stored in a DB2 table should always be stored using one of the chronological data types provided by DB2 (that is, DATE or TIMESTAMP). But, enough whining, how do we attack this issue without re-designing the database?

Well, the solution resides in translating the non-DATE column into a DB2 date. You can use the SUBSTR function to break the character column apart into the separate components, for example SUBSTR(column,1,4) returns the year component, SUBSTR(column,5,2) returns the month, and SUBSTR(column,7,2) returns the day. Then you can concatenate all of these together into a format that DB2 recognizes, for example, the USA format which is mm/DD/yyyy. This can be done as follows:

SUBSTR(column,5,2) || "/" || SUBSTR(column,7,2) || "/" || SUBSTR(column,1,4)

Then you can use the DATE function to convert this character string into a DATE that DB2 will recognize. This is done as follows:

DATE(SUBSTR(column,5,2) || "/" || SUBSTR(column,7,2) || "/" || 
        SUBSTR(column,1,4))

The result of this can be used in date arithmetic with other dates or date durations. Or it can be used in SQL predicates for evaluation. Of course, it may not perform extremely well, but it should return the results you desire.

Date Arithmetic

Another challenging aspect of manipulating dates in a database is how handle date arithmetic. DB2 enables you to add and subtract DATE, TIME, and TIMESTAMP columns. In addition, you can add date and time durations to or subtract them from these columns. But use date and time arithmetic with care. If you do not understand the capabilities and features of date and time arithmetic, you will likely encounter some problems implementing it.

When you issue date arithmetic statements using durations, do not try to establish a common conversion factor between durations of different types. For example, the following two date arithmetic statements are not equivalent:

1997/04/03 - 1 MONTH

1997/04/03 - 30 DAYS

April has 30 days, so the normal response would be to subtract 30 days to subtract one month. The result of the first statement is 1997/03/03, but the result of the second statement is 1997/03/04. In general, use like durations (for example, use months or use days, but not both) when you issue date arithmetic.

If one operand is a date, the other operand must be a date or a date duration. If one operand is a time, the other operand must be a time or a time duration. You cannot mix durations and data types with date and time arithmetic.

If one operand is a timestamp, the other operand can be a time, a date, a time duration, or a date duration. The second operand cannot be a timestamp. You can mix date and time durations with timestamp data types.

Now, what exactly is in that field returned as the result of a date or time calculation? Simply stated, it is a duration. There are three types of durations: date durations, time durations, and labeled durations.

Date durations are expressed as a DECIMAL(8,0) number. To be properly interpreted, the number must have the format yyyymmdd, where yyyy represents the number of years, mm the number of months, and DD the number of days. The result of subtracting one DATE value from another is a date duration.

Time durations are expressed as a DECIMAL(6,0) number. To be properly interpreted, the number must have the format hhmmss, where hh represents the number of hours, mm the number of minutes, and ss the number of seconds. The result of subtracting one TIME value from another is a time duration.

Labeled durations represent a specific unit of time as expressed by a number followed by one of the seven duration keywords: YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, or MICROSECONDS. A labeled duration can only be used as an operand of an arithmetic operator, and the other operand must have a data type of DATE, TIME, or TIMESTAMP. For example:

CURRENT DATE + 3 YEARS + 6 MONTHS

This will add three and a half years to the current date.

Converting to a Common Denominator

Sometimes it can get confusing trying to interpret the results of DB2 date arithmetic. Is there a way in DB2 date arithmetic to express the duration resulting from date subtraction, as a total-number-of-days (exact total, and not an approximate total)? To illustrate, consider this query:

SELECT DATE ('03/01/2004') - '12/01/2003'

which returns a duration of 00000300 (i.e. 3-months). And those 3-months encompass a 29-day February plus a 31-day January plus a 31-day December (total 91 days). How can we write a query to return the number of days, that is 91?

Well, the answer lies in using another DB2 function named DAYS. The DAYS function converts a date into the number of days since day zero. Or, another way of saying it, the result of this function is 1 more than the number of days since January 1, 0001.

So, the following query will return the number of days between two dates:

SELECT DAYS('03/01/2004') - DAYS('12/01/2003')

Summary

DB2 offers a vast array of functionality to handle dates. It is appropriate to use the DATE data type when you store dates in DB2 tables. It simplifies life later on when you want to do things like date arithmetic. Doing so also ensures that DB2 will perform the proper integrity checks on the columns when data is entered, instead of requiring application logic to ensure that valid dates are entered.




Craig Mullins


Craig S. Mullins is a data management strategist, researcher and consultant. He is president and principal consultant of Mullins Consulting, Inc., and has nearly three decades of experience in all facets of database systems development.

Craig has worked with mainframe DB2 since V1 and has experience with several other DBMS products, including SQL Server, Sybase and IMS. You may know Craig from his popular books: DB2 Developer's Guide, which has more than 1,500 pages of in-depth technical information on DB2 for z/OS, and Database Administration: The Complete Guide to Practices and Procedures - the industry's only comprehensive guide to heterogeneous database administration.

More information on Craig is available at www.craigsmullins.com.



comments powered by Disqus