August 2002 Technical Tip: Storing Dates in DB2

August 2002 Technical Tip: Storing Dates in DB2

Consistent with this month's special offer for DB2 training, this month's technical tip deals with storing dates in DB2. First, one has to decide the format in which the date will be stored. There are several options. One option is to use a CHAR(6) or CHAR(8) field. Hopefully, the CHAR(6) option is quickly discarded: we should have learned our lesson with Y2K! If the date is stored as CHAR(8), one must choose between mmddccyy, ccyymmdd, etc. I have seen companies use CHAR(6) before, and I suspect this was because (1) the source data was known to have plenty of errors, or (2) to save space. If a date field is stored as CHAR(6), you'll need to keep the following in mind:

Dates will be INSERTed as '122692', '010293', '010993'.
There is no validation (aka domain integrity.)
Within a COBOL program the host variable should be PIC X(6).
Beware the century!
It is difficult to sort or ORDER BY properly.
You can't do date arithmetic.
Use wildcards to retrieve specific dates. For example, to get 1993 records only use WHERE ADATE LIKE '____93' (four underscores) or WHERE ADATE LIKE '%93%'

The best option is to store the date as a DATE field. In this case,

Dates will be INSERTed as '1992-12-26', '1993-01-02', '1993-01-09'. Note the required use of hyphens.
There is automatic validation (aka domain integrity.)
Within a COBOL program the host variable should be PIC X(10) (not PIC X(8) as one might suspect!)
You can do date arithmetic. For example, DAYS(ADATE) returns the number of days between 01/01/0000 and ADATE. Likewise, DAYS(FIRSTDATE) – DAYS(SECONDDATE) returns the number of days between FIRSTDATE and SECONDDATE.
Use the builitin functions YEAR(), MONTH(), and DAY() to retrieve specific dates. For example, to get 1993 records only use WHERE YEAR(ADATE) = 1993. Note these functions return a number.

If you want full life cycle DB2 training, then give us a call. Our DB2 course makes extensive use of hands-on examples and exercises to familiarize the student with the fundamentals of relational database concepts in general and DB2 in particular. First, the student is introduced to relational database design concepts, including the creation of entity relationship diagrams (ERDs) and how to reduce ERDs to relations in third normal form (3NF). Then, the student will use DB2's data definition language (DDL) to create the actual tables. Next, the student will use DB2's data manipulation language (DML) to query the tables. Finally, the student will learn how to code embedded SQL within COBOL programs. In this course we do it all, from analysis to data modeling to creating the tables to querying the tables to accessing the tables through programs! We hope you will consider Caliber Data Training for your DB2 training and development needs!