Click here to Skip to main content
13,513,747 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


Posted 7 Oct 2011

CYYMMDD (Integer) to DATE in DB2 for iSeries

, 7 Oct 2011
Rate this:
Please Sign up or sign in to vote.
Conversion of 7 digit integer date format to SQL date format


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)|| '-' ||

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)


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior)
India India
I am a Java/J2EE programmer and DB2 DBA with 4+ years of experience, working for an MNC in India. My area of interests includes Struts, Spring, iBatis (myBatis), JavaScript, HTML(5), jQuery, SQL tuning, C, Linux. I spend my free time learning, listening and playing music and help to publish my dad's translation works on Internet.

I enjoy learning new things in an abstract manner and apply them in wide variety of areas from cooking my supper to writing a program.

As an Electronics Engineering graduate, PCBs and chips share a good portion of my desk's draw. Currently doing my masters in IT. Hoping to pursue PhD in Computer Science/IT.

You may also be interested in...

Comments and Discussions

GeneralCYYMMDD (Integer) to DATE in DB2 for iSeries Pin
NickSwann26-Jun-14 23:52
memberNickSwann26-Jun-14 23:52 
Suggestioncyymmdd when value is zero Pin
turtlkky24-Jan-13 5:47
memberturtlkky24-Jan-13 5:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.180417.1 | Last Updated 7 Oct 2011
Article Copyright 2011 by davidshenba
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid