CYYMMDD (Integer) to DATE in DB2 for iSeries

Introduction

There is a date format in DB2 for iSeries, CYYMMDD. Technically it is an integer and hence comparison of dates becomes easier and faster job for the machine. The aim of this post is to demonstrate how to convert this CYYMMDD date into a SQL date. This is to ensure that the date is presented to the user in an understandable format and avoid type mismatches while dealing with languages like Java (java.sql.Date).

Understanding The Problem

First, let us understand the format. C YY MM DD. We all can easily get that YY is the last two digits of year, MM is the month and DD is the date. What is that 'C'? As some of you had rightly guessed, it is century. But what century? It is century counted from 1900. So 1900-1999 is 0th century, 2000-2099 is 1st and so on. So, for example, 31st December of 1991 will be written as '0911231'. Likewise 1st of January 2003 will be written as '1030101'.

So the first three digits from left, added with 1900 will give you the year (1900+103 = 2003).

Next two digits gives you the month
Last two digits gives you the date

One simple approach to this problem is to do substring of the incoming 7 character long string and do the necessary type casting before adding the century-year with 1900, then inserting hyphen (-) at appropriate places, before typecasting the entire thing into SQL DATE format.

Note: SQL DATE typecasting can be done by passing date string in YYYY-MM-DD format to the DATE() function.

Some Special Cases

But there is a catch here. As the value stored in the database is integer, it needs a pre-typecasting to string before we do the substring operations. OK, let us assume we typecast the integer from a query/parameter into string and then do substring. Will it work for all cases? Because, preceding zeros are omitted while saving/processing the data as integer and '0911231' will come as a six digit '911231'. Here we can avoid the problem by checking the digits. If it is 7, then do the substring for digits (1,3), (3,5), (5,7). Else do substring for digits (1,2), (2,4), (2,6). Problem solved? The answer is NO!

What about 23rd of February1909? It will come as '90223' a five digit number. Another "else if"? NAH!!! If you are a programmer, please don't consider individual cases and write any code. First understand what is expected and what are all possible inputs we can expect. The best approach to start with is the boundary conditions approach.

As it was explained above, it is a 7 digit integer (positive numbers only). Century is counted from 1900. So dates lesser than 1900 can never come into the picture and even if some dumbass pass negative values, they deserve to be thrown an incorrect result or error (whatever!). Least possible date is 1st January of 1900 that is '101' (note that it is only three digits, if you take up and proceed with the earlier approach, you will end up if/else if/else for 7, 6, 5, 4 and 3 digits and if Linus Torvalds happens to see what you have done, your probability of entering heaven is "nolla".) Now the largest number possible is 31st of December 2899 (999+1900). As the format allows only 7 digits, no further dates can be represented in CYYMMDD format.

The Solution

So, whatever the input (from 101 to 9991231) I need date. Fine, so I need the century year (that is anything that remains after removing last four digits). So just divide by 10000. Then add it with 1900.

In case of '101' it will be 0+1900=1900 and in case of '1010101' it will be 101+1900=2001. (Integer division NEVER produce decimal results!)

Fine, we got the year. Next let us go for the month. It is in the middle (bit tricky here). I find that MOD function in DB2 is handy here. MOD(NUMBER, 10000) will give last four digits and a division by 100 will give the first two of the last four digits. So, the formula for month is now MOD(NUMBER, 10000)/100.

In case of '101', it will be MOD(101, 10000) = 101. Then 101/100 = 1. This your month. What about 9991231? MOD(9991231,10000) = 1231. Then 1231/100 = 12. This is your month. (again, integer division NEVER produces decimal results!)

So the last two digits, which are date alone remain to be handled now. Again we call upon our trustworthy ally MOD. MOD(NUMBER, 100) will give you the date, whatever may be the case. I think I can safely ignore providing examples on how MOD works with 100 on various digits.

Then we have to place hyphens in between year, month and date and construct a string that will be typecast to date. Did that work?

SELECT DATE(((NUMBER/10000)+1900)|| '-' ||
(MOD(NUMBER,10000) /100)|| '-' ||
MOD(NUMBER,100))
FROM
SYSIBM.SYSDUMMY1;

Where NUMBER is your CYYMMDD integer.

Why don't you write your own custom function to return a DATE for any CYYMMDD date passed as integer if you are going to use this often?

-Shenbaga Murugan Paramasivapandian

(DB2 database administrator, Java/Web developer, Open Source enthusiast)