Re: SQL join date field with numeric field

[Jason Abreu <jason.abreu@xxxxxxxxxxxxxxxxxxxx>]

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