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, STORENAME, date(substr(I_STOREOPEN, 1, 4) || '-' || substr(I_STOREOPEN, 5, 2) || '-' || substr(I_STOREOPEN, 7, 2)) from STORES join ISODATE on STORENO = I_STORENO
In order to improve the clarity a bit, I have broken it up a bit:
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
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.
- A STOREOPEN date of 1st February 2003 would be represented in the STORES table as 30201
withclause builds table ISODATE in which I_STORENO is represented as 20030201
substrand concatenation functions generate a character representation of 2003-02-01 and then passes this to the
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.