× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.