Legacy Dates to SQL Dates

Hey, Howard:

-- Joe

There is always more than one way to skin a cat, so let's look at how your dates are stored and talk about ways to get temporal information out of them.

First, your format is one that I have seen often and is quite easy to work with.

The dates are stored as Century, Year, Month, and Day, where Century is 0 for 19 and 1 for 20, years are stored as two digits, and months and days always have leading zeros.

Here is a table of sample data:

Log Number	Decimal Date	Date Represented
1096	821231	December 31, 1982
1461	831231	December 31, 1983
2192	851231	December 31, 1985
7305	991231	December 31, 1999
7306	1000101	January 1, 2000
10228	1080101	January 1, 2008

One way to extract portions of a date is with division and modulo arithmetic. For example, the formula to extract a month from the above data is X MOD 10000 / 100. If the number is 831231, the modulo operation results in the value 1231. Dividing 1231 by 100 results in 12.31. Of course, I do not want the fractional portion, so I can use TRUNCATE or cast the value to INTEGER in order to end up with the whole number portion only. So, in a WHERE clause of a statement, if I want to match all dates where the month is 12, I might use the following SQL statement:

WHERE INTEGER(MOD(YMD,10000)/100)=12

The formula for extracting the date part is X MOD 100. Again, to use this in a WHERE clause, the following statement would match month 4 and day 2:

WHERE INTEGER(MOD(YMD,10000)/100)=4 AND
	INTEGER(MOD(YMD,100))=2

Now we get to the year formula, which is slightly harder. To get the year part, the formula is:

(X MOD 1000000/10000) + 1900 +
 (100 * INTEGER(X  / 1000000)

Assume X = 1080101. X MOD 1000000 yields 80101, which, when divided by 10000, yields 8. Adding 1900 to 8 gives us 1908. Now, we need to add the extra 100 years indicated by the century flag at the beginning of the number. By dividing X by 1,000,000, the yield is the value 1 or less. By casting to integer, if the number is less than 1, it becomes a zero, whereas if it is greater than 1, it is truncated to 1. The result * 100 indicates how many years to add to the date in order to adjust for the century flag. So, if you wanted to look activity in the year 2000, your WHERE clause might look like this:

	WHERE INTEGER(MOD(YMD,1000000)/10000+1900+
		(100*INTEGER(YMD/1000000))) = 2000

Now, there are other ways that you could deal with this data, but the most interesting thing to do is convert the legacy number to an SQL date so that you can do date math and use the build-in-date scalar functions. One way to convert is to use my wonder MOD and INTEGER math trick, but Ted Holt pointed out an even easier way to convert the date using simple addition and a sub-string operation. By adding 19000000 to the date field, you create a number that can be easily substringed to get the relevant year, mont and day. Here is a snippet of SQL that converts the field to a date using ISO format:

DATE(
      SUBSTR(CHAR(19000000 + YMD),1,4) ||’-‘||
      SUBSTR(CHAR(19000000 + YMD),5,2) ||’-‘||
      SUBSTR(CHAR(19000000 + YMD),7,2)
      )

If your SQL session format is *USA and the date separator is a forward slash (/), you could also convert the date this way:

DATE(
      SUBSTR(CHAR(19000000 + YMD),5,2) ||’/‘||
      SUBSTR(CHAR(19000000 + YMD),7,2) ||’/‘||
      SUBSTR(CHAR(19000000 + YMD),1,4)
      )

If you like integers, parentheses, and mods, you could convert the date this way:

DATE(
TRIM(CHAR(INTEGER(MOD(YMD,10000)/100)))||'/'||
TRIM(CHAR(INTEGER(MOD(YMD,100))))||'/'||
TRIM(CHAR(INTEGER(mod(ymd,1000000)/10000+1900+(100*INTEGE

R(ymd/1000000)))))) 

Here is a sample table and insert statements to execute against your DB2/400 database so that you can execute the next set of queries in this article:

CREATE TABLE weboe/h1t2
(log DECIMAL(5,0),ymd DECIMAL(7,0));

INSERT INTO weboe/h1t2 VALUES (1096,821231);
INSERT INTO weboe/h1t2 VALUES (1461,831231);
INSERT INTO weboe/h1t2 VALUES (2192,851231);
INSERT INTO weboe/h1t2 VALUES (7305,991231);
INSERT INTO weboe/h1t2 VALUES (7306,1000101);
INSERT INTO weboe/h1t2 VALUES (10228,1080101);

The above queries create a table in the library WEBOE called H1T2. They then place a set of dates into the tables consisting of log numbers and dates. The following query extracts the value of the YMD column as an SQL date:

SELECT log,ymd,
DATE(
      SUBSTR(CHAR(19000000 + YMD),5,2) ||’/‘||
      SUBSTR(CHAR(19000000 + YMD),7,2) ||’/‘||
      SUBSTR(CHAR(19000000 + YMD),1,4)
      )
 AS MYDATE
FROM weboe/h1t2

Running the above query against the sample data yields the following results:

LOG	YMD	MYDATE
1096	821231	12/31/1982
1461	831231	12/31/1983
2192	851231	12/31/1985
7305	991231	12/31/1999
7306	1000101	01/01/2000
10228	1080101	01/01/2008

Consider using the statement as a common table expression if you need to manipulate the data using different scalar functions. The following query executes the conversion function one time within the common table expression and then the resultant value is used in the outer query in several formulas:

SELECT log, ymd, MYDATE,
   DAYOFWEEK(MYDATE) AS DOW, 
   days(CURRENT_DATE) - days(MYDATE) AS DAYS_SINCE_EVENT,
   quarter(MYDATE) AS EVENT_QUARTER,
   WEEK(MYDATE) AS EVENT_WEEK
FROM 
(
   SELECT log,ymd,
DATE(
      SUBSTR(CHAR(19000000 + YMD),5,2) ||’/‘||
      SUBSTR(CHAR(19000000 + YMD),7,2) ||’/‘||
      SUBSTR(CHAR(19000000 + YMD),1,4)
      )
 AS MYDATE
    FROM weboe/h1t2
) AS X;

Here is the output from the above query:

LOG	YMD	MYDATE	DOW	DAYS_SINCE	EVENT_QUARTER	EVENT_WEEK
1096	821231	12/31/1982	6	7052	4	53
1461	831231	12/31/1983	7	6687	4	53
2192	851231	12/31/1985	3	5956	4	53
7305	991231	12/31/1999	6	843	4	53
7306	1000101	01/01/2000	7	842	1	1
10228	1080101	01/01/2008	3	-2080	1	1

SELECT * FROM WEBoe/H1T2 WHERE
CURRENT_DATE = 
DATE(
      SUBSTR(CHAR(19000000 + YMD),5,2) ||’/‘||
      SUBSTR(CHAR(19000000 + YMD),7,2) ||’/‘||
      SUBSTR(CHAR(19000000 + YMD),1,4)
      )

If you want more information on manipulating legacy dates, see my Web pages at www.sqlthing.com or read the chapter on advanced SQL in my book, iSeries and AS/400 SQL at Work , which can be purchased through my Web site .

-- Howard