One of the areas I get a lot of questions about is on proper DATE usage and formatting in DB2. The ability to store dates as a native DB2 data type is a great advantage. If you need to store date information in your DB2 tables you should favor using the DATE data type instead of forcing a date into some other data type (such as CHAR or DECIMAL). When DB2 knows that the data should be a DATE it can force data integrity such that no non-date value could ever be stored in the column. This is a big advantage, but it is not the only one. DB2 allows users to perform date arithmetic. So, you can easily use these date columns to calculate durations or past and future dates based on a number of days, months, and years. Think about the application code you would have to write to do that!

Now let's delve into some of the common questions about DB2 dates.

Q: I have a DATE column in a DB2 table, but I do not want it to display the way DB2 displays it by default. How can I get a date format retrieved from a column in a table from DB2 database in the format MM/DD/YYYY?

A: The simplest way to return a date in the format you desire is to use the built-in column function CHAR. Using this function you can convert a date column into any number of formats. The specific format you request, MM/DD/YYYY, is the USA date format. So, for example, to return the date in the format you requested for a column named START_DATE you would code the function as follows:

     CHAR(START_DATE,USA)

The first argument is the column name and the second argument is the format. Consult Table 1 for a list of the date formats that are supported by DB2. You may also have an installation-defined date format that would be named LOCAL. For LOCAL, the date exit for ASCII data is DSNXVDTA, the date exit for EBCDIC is DSNXVDTX, and the date exit for Unicode is DSNXVDTU.

Table 1: DB2 DATE formats.

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) || "/index.html" || SUBSTR(column,7,2) || "/index.html" || 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) || "/index.html" || SUBSTR(column,7,2) || "/index.html" || 
                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.

Q: I'm confused about how DB2 handles date arithmetic! It seems that I can subtract a date from a date, or a time from a time, but what will DB2 return as the result of such a calculation? Please help?

A: 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.

Keep the following rules in mind:

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.

Q: 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, the query:

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

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). So I would be looking for a query which would return the number 91. Any ideas?

A: The answer lies in using the DAYS function. The following should return what you need:

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

This query will return to you the exact number of days between the two dates.

Q: I want subtract a variable from the current date. Depending upon certain criteria, I want to subtract a number of days from the date, but I am getting an error: undefined or unusable variable. My code is:

     SELECT A.DOC, A.TRAN_DATE, A.CRTE_DATE..
     FROM ABC.TODDOC A,.....
     WHERE  A.CMPY = B.CMPY AND ....
     AND (A.CRTE_DATE <= CURRENT DATE - :SUB2 DAYS) AND 
              (A.CRTE_DATE > CURRENT DATE - :SUB3 DAYS)   AND  .....

The SUB2 and SUB3 host variables are day-of-week fields. Please, help… what is the correct syntax or alternative solution?

A: You cannot use a labeled duration with a host variable like that. The solution is to use a date duration. A date duration represents a number of years, months, and days 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. So, your query would become:

     SELECT A.DOC, A.TRAN_DATE, A.CRTE_DATE..
     FROM ABC.TODDOC A,.....
     WHERE  A.CMPY = B.CMPY AND ....
     AND (A.CRTE_DATE <= CURRENT DATE - :SUB2) AND 
              (A.CRTE_DATE > CURRENT DATE - :SUB3)   AND  .....

And :SUB2 and :SUB3 become DECIMAL(8,0) date durations. For example, to specify 3 DAYS as the date duration, you would use the value 00000003. This specifies 0 years, 0 months, and 3 days.

Q: I want to enter date in format mm-dd-yyyy hh:mm:Ss in DB2. How can I do that?

A: Well, first of all, in DB2 the combination of date and time is known as a TIMESTAMP data type, so you will need to use TIMESTAMP as the data type instead of DATE (which is just the date with no time component). Other DBMSs use the DATE data type to store both date and time data, but not DB2.

     YYYY is year
     MM is month
     DD is day
     HH is hour
     MM is minutes
     SS is seconds and
     mmmmmm is microseconds 

That is the only acceptable format of DB2 timestamp data.

Q: I want to fetch a date column with the 'DD-MMM-YYYY' format. Can I do it in a single query? I tried to run the following SQL, but got an error:

     SELECT  CHAR(T1.COL_DT ,'DD-MMM-YYYY'),
     FROM      TABLE1 T1;         

Any ideas?

A: You are not using the CHAR function correctly. When using CHAR to convert a date column into a character representation, the second parameter can only be one of the following: ISO, USA, EUR, JIS, or LOCAL. Consult Table 1 for the format to which each of these corresponds.

To return the data in the format you are requesting, DD-MMM-YYYY, you will need to install a local date format routine. Get in touch with your system programmer to discuss whether this is feasible and how to do it.

Q: I need to convert my date, time, and timestamp column values into their separate components. What facilities does DB2 provide to allow me to do this?

A: DB2 provides a vast number of functions that can be applied to DATE, TIME, and TIMESTAMP columns to help you. Chances are you will find a function to help you with your task at hand, depending on your specific needs. Consider the following functions:

Summary

Using date and time data correctly in DB2 can be a bit confusing, but the rewards of learning proper date and time usage are numerous. Do not continue to operate in the void. The wise DB2 professional will learn proper DB2 date and time usage and the vast support built into DB2 for manipulating date and time values. And the sooner, the better!

--

Craig Mullins is an independent consultant and president of Mullins Consulting, Inc. Craig has extensive experience in the field of database management having worked as an application developer, a DBA, and an instructor with multiple database management systems including DB2, Sybase, and SQL Server. Craig is also the author of the DB2 Developer’s Guide, the industry-leading book on DB2 for z/OS, and Database Administration: Practices and Procedures, the industry’s only book on heterogeneous DBA procedures. You can contact Craig via his web site at http://www.craigsmullins.com.