©  2021  Mullins Consulting, Inc.   All Rights Reserved   Privacy Policy   Contact Us

The Last Date of the Century

by Craig S. Mullins

There has been an interesting on-going date-related thread in the SQL-L mailing list. Many responses have been generated based on a simple question, namely:

“How can I get the last date of the century by SQL Query.”

There has been a lot of discussions and variations on the question, as well as some good code for SQL Server. I thought it might be worthwhile to try to answer this for DB2 for z/OS, so here goes.

Step 1: Define Century

First of all, we need to answer the question, “How do you define century?” I know,
I know, for those sticklers out there you are saying that there is one accurate answer
to that question. But as anyone who lived through the whole Year 2000 crisis knows,
different folks define the end of the century differently. Some are completely convinced
that the last year of last century was 1999, while the sticklers know it actually ended at
the end of 2000.

If you are unconvinced, think it through. When you count to one hundred, you start with
one, not zero. So the first one hundred numbers you count end with 100. The next one
hundred numbers starts with 101. A century consists of 100 years. And there was no
such thing as a year 0. So, the first century ended at the end of year 100. Count on your
fingers if you have to… 1, 2, 3, … 99, 100. Add 100 years and the second century ends at
the end of the year 200, and on and on. Do the math. The 20th century ended at the end
of the year 2000. The 21st century began with the year 2001.

OK, so we will answer this question knowing that the end of the century will always end with double zeroes (if you are stubborn and continue to disagree you can just twiddle with the code and make it work for double nines).

Step 2: Define Date

The next step is to define what is meant by date. The usual answer would be month, day, and year. But as those of us who troll the online newsgroups and mailing lists know, questions posed online are anything but precise. This particular question may have been asking for the day of the week of the last day of the century – expecting the answer of the query to be Monday, Tuesday, Wednesday, etc.

Let’s try to answer both.

Step 3: Return the Current Date

Before we can determine the last date of the century we have to get a starting place, and that is today’s date. Different DBMS products return the current date in different ways, but DB2 uses the CURRENT DATE special register. Appropriately enough, the data type returned by this special register is a DB2 DATE.

Step 4: Isolate the Century Component of the Year

So we need to code a method that takes the CURRENT DATE and pulls off just the first two characters of the year component. We start by isolating just the year. This can be done using the scalar function named YEAR, as follows:

     YEAR(CURRENT DATE)

The result of the function is a large integer. Next we need to return just the first two digits of the year. This is the key to the current century we are in. This can be done with any understanding of how DB2 arithmetic works. The result of dividing an integer by an integer will return an integer. This means that any result after the decimal point is truncated. If we divide the year by 100 the result will just be the first two digits. For example:

     YEAR(CURRENT DATE) / 100

     2006 / 100 = 20      (the .06 remainder is truncated because the result is an integer)

Alternately, we could have converted the year to a string using the CHAR function and then used the SUBSTR function to return just the first two digits, as follows:

     SUBSTR(CHAR(YEAR(CURRENT DATE)) ,1,2)

The result of this is a character string containing just the first two digits of year.

Step 5: Add One

Since we are following the standard of the century ending in 00 (not 99), we need to add one to our two digit century. For example, if our result is 20 we need it to become 21. This approach works until the 100th century – let’s not worry about that too much as I’m sure none of us will be around when that happens (“Hey, isn’t that what caused the Year 2000 problem?”)

So, the code for this step is:

     (YEAR(CURRENT DATE) / 100) + 1

Step 6: Convert the Number to a String

As we learned above, we can convert a number to a string using the CHAR function. So the code for this step is:

     CHAR((YEAR(CURRENT DATE) / 100) + 1)

Step 7: Formulate the Last Date of the Century

From here on out things are not too difficult. As long as we continue to use the current calendar system we know that the last month of the year is always December (12); and we know the last day of December is always the thirty-first (31).

Taking this knowledge, and the formula from Step 4, we can create our last date of the century as follows:

     '12-31-'  || CHAR((YEAR(CURRENT DATE) / 100) + 1) || '00'

For those who do not know, the || operator is the concatenation operator. You can alternately use CONCAT instead of the vertical bars.

Summary

Many problems of this nature are doable using some combination of SQL and functions. Many new and useful functions have been added to DB2 over the course of the past few releases, so if you haven’t reviewed the functions available to you in DB2 in awhile it is a good idea to download the SQL Reference manual and read through the sections on functions. You might be able to turn an unsolvable problem into a solved one using nothing but SQL.

May 2006

From DB2 Update, May 2006.

© 2012 Craig S. Mullins,