Re: SQL join date field with numeric field

Any conversion process would have to be carefully looked at to see the effect of null-value or invalid post-conversion data; whether it be a manual process, UDF or View.

There are some good ideas posted for comparison. I think the biggest deciding factor of how complex you want your comparison logic to be depends on the frequency of your comparison.
Is this a one-time old-to-new translation, as your pseudo-table names suggest? In that case a UDF or View may not be your best option. However, if this is a recurring comparison then your best option would probably be a View, followed shortly by the UDF.

Jason Abreu
Abreu Innovations, Inc.
jason.abreu@xxxxxxxxxxxxxxxxxxxx
http://www.abreuinnovations.com/

On 2/14/2011 9:53 AM, rob@xxxxxxxxx wrote:

Jason's example may work but pay attention to his caveats. Instead, I
recommend using a UDF or User Defined Function, to get this done. Your
UDF should handle your corporate policies as to how to handle dates that
are invalid, stored as null, etc. You may not use nulls at this time but
with a UDF you built it in now and not have to worry about the negative
ramifications in the future. Alan Campin has some date routines out there
on the internet somewhere which are quite useful.
Doing this will also reduce possible debugging and what now in all the
places which use these numeric dates. For example, if you use
SELECT *
FROM oldtable join newtable
on newDate = NumToDate(oldDateAsNbr)
The chances of that getting a wrong apostrophe or whatnot reduce
dramatically.

Now, there is also a performance consideration. If the data is
overwhelmingly stored in the numeric date format it may behoove you to try
SELECT *
FROM oldtable join newtable
on oldDateAsNbr = DateToNum(newDate)
Using the inverse of NumToDate, DateToNum. This may take advantage of a
key on date (if it exists).

I had trouble trying this:
CREATE INDEX ROB/oldtablel01
ON ROB/OLDTABLE
(numtodate(OLDDATEASNBR) ASC)

But I could get this to work:
CREATE INDEX ROB/oldtablel01
ON ROB/OLDTABLE
(DATE(SUBSTR(CHAR(oldDateAsNbr),1,4)||'-'||
SUBSTR(CHAR(oldDateAsNbr),5,2)||'-'||
SUBSTR(CHAR(oldDateAsNbr),7,2) ))

Rob Berendt