(Continued from previous part...)

Q: Well, that is nice, but my format does not fit into any of these listed here. What if I have a DATE stored like YYYYMMDD (with no dashes or slashes) and I want to compare it to a DB2 date?

A: Okay, let's look at one potential solution to your problem (and then I want to briefly talk about the use of proper data types). First of all you indicate that your date column contains dates in the following format: yyyymmdd with no dashes or slashes. You do not indicate whether this field is a numeric or character field - I will assume that it is character. If it is not you can use the CHAR function to convert it to a character string.

Then, 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. Of course, it may not perform extremely well, but it should return the results you desire.

Now, a quick word about using proper data types. As I mentioned at the beginning of this article, it is wise 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.

(Continued on next part...)