Re: creating a log spool file for backups

Here's an update:

SELECT ODLBNM, ODOBNM, ODOBTP, ODOBAT, ODSDAT, 
case 
  when substr(odsdat,5,2)='  ' then 
       date('01/01/1801') 
  when substr(odsdat,5,2)>'06' then 
       date(substr(odsdat,1,2) concat '/' concat
            substr(odsdat,3,2) concat '/' concat
            '19' concat 
            substr(odsdat,5,2)) 
  else 
       date(substr(odsdat,1,2) concat '/' concat
            substr(odsdat,3,2) concat '/' concat
            '20' concat 
            substr(odsdat,5,2)) 
  end as SavedDate, 
case 
  when substr(odldat,5,2)='  ' then 
       date('01/01/1801') 
  when substr(odldat,5,2)>'06' then 
       date(substr(odldat,1,2) concat '/' concat
            substr(odldat,3,2) concat '/' concat
            '19' concat 
            substr(odldat,5,2)) 
  else 
       date(substr(odldat,1,2) concat '/' concat
            substr(odldat,3,2) concat '/' concat
            '20' concat 
            substr(odldat,5,2)) 
  end as ChangedDate 
from qtemp/dskdtl 
order by SavedDate 


Too bad you can't do (see the "where" clause):

SELECT ODLBNM, ODOBNM, ODOBTP, ODOBAT, ODSDAT, 
case 
  when substr(odsdat,5,2)='  ' then 
       date('01/01/1801') 
  when substr(odsdat,5,2)>'06' then 
       date(substr(odsdat,1,2) concat '/' concat
            substr(odsdat,3,2) concat '/' concat
            '19' concat 
            substr(odsdat,5,2)) 
  else 
       date(substr(odsdat,1,2) concat '/' concat
            substr(odsdat,3,2) concat '/' concat
            '20' concat 
            substr(odsdat,5,2)) 
  end as SavedDate, 
case 
  when substr(odldat,5,2)='  ' then 
       date('01/01/1801') 
  when substr(odldat,5,2)>'06' then 
       date(substr(odldat,1,2) concat '/' concat
            substr(odldat,3,2) concat '/' concat
            '19' concat 
            substr(odldat,5,2)) 
  else 
       date(substr(odldat,1,2) concat '/' concat
            substr(odldat,3,2) concat '/' concat
            '20' concat 
            substr(odldat,5,2)) 
  end as ChangedDate 
from qtemp/dskdtl 
where SavedDate<ChangedDate
order by SavedDate 

Rob Berendt