RE: Another SQL question.

Yes.

Assumptions:
- FILEA.DATE1 is in format MMDDYYYY
- FILEB.DATE2 is in format MMDDYY

NOTE: you WILL need to massage the UPDATE statement to match exactly what
you need.

Easiest may be to compare them as numeric values, rather than dates:

UPDATE FILEA a SET F1 = 'New value'
WHERE EXISTS (
  SELECT 1 
  FROM FILEB b 
  WHERE CAST(SUBSTR(a.DATE1,1,4) || SUBSTR(a.DATE1,7) AS NUMERIC(6)) 
        = b.DATE2
)

However, if you really want to transform both fields to DATE types, you
could do something like this:

UPDATE FILEA a SET F1 = 'New value'
WHERE EXISTS (
  SELECT 1 
  FROM FILEB b 
  WHERE DATE(SUBSTR(a.DATE1,1,2) || '/' || 
             SUBSTR(a.DATE1,3,2) || '/' ||
             SUBSTR(a.DATE1,5)) = 
        DATE(SUBSTR(DIGITS(b.DATE2),1,2) || '/' ||
             SUBSTR(DIGITS(b.DATE2),3,2) || '/' || 
             SUBSTR(DIGITS(b.DATE2),5))
)

Hope that gives you an idea of how to approach it.


-----Original Message-----
Subject: Another SQL question.

I need to update fields in FILEA based on a date comparison with FILEB.
However, the date in FILEA is defined as 8A and the date in FILEB is
defined as 6S.  Is it possible to compare these fields even though they
are not the same format?

It's a one-time fix and I was hoping avoid writing a program.

Thanks. 

Sheri Rowe
Timex Canada