Getting an ISO date from a 6 digit number – The lazy approach

Someone asked me today if I had an SQL function to convert a six digit number into an ISO date. I don’t because if you store dates in date fields then there is no need to convert the date back into a date.

But we have a legacy table. And this table stores a date as a six digit number which represents YYMMDD. I’m not going to write a function for this, but the approach is pretty simple, and shown below:

with ISODATE as
 (select STORENO as I_STORENO,
 char(20000000 + STOREOPEN) as I_STOREOPEN
 from STORES)
select STORENO, 
       date(substr(I_STOREOPEN, 1, 4) || '-' || 
            substr(I_STOREOPEN, 5, 2) || '-' || 
            substr(I_STOREOPEN, 7, 2)) 
from STORES 

In order to improve the clarity a bit, I have broken it up a bit:

The with clause builds a temporary table of store numbers and nicely formatted opening dates. This converts the six digit date into an eight character string which is almost good enough to pass to the build in date function.

The main select then splits the date into its component parts and then concatenates these (with a ‘-‘ separator) so that the date can handle it.

For example:

  • A STOREOPEN date of 1st February 2003 would be represented in the STORES table as 30201
  • The with clause builds table ISODATE in which I_STORENO is represented as 20030201
  • The substr and concatenation functions generate a character representation of 2003-02-01 and then passes this to the date function

And now you have a value on which normal date calculations can be performed.

Of course, the better approach would be to replace these legacy tables in order to make this sort of manipulation unnecessary.

2 thoughts on “Getting an ISO date from a 6 digit number – The lazy approach

  1. At my work, where we have an AS/400 as our base for production (also have MSSQL mostly for reporting, Oracle for more confusion added…), I’m all too familiar with converting dates. Problem is with our system, is that there isn’t any consistency with any of the date fields. We’ve got YYMMDD, YYYYMMDD, YYYY-MM-DD, and a few strange Julian-like fields that don’t follow any sort of convention. Highly frustrating. Damn these legacy tables…

    Anyway, from experience, I can say that you’re statement above does work quite well. 🙂


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s