RE: 8s0 field into a date using SQL

forget that I didn't read it properly

-----Oorspronkelijk bericht-----
Van: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Namens Raby, Steve
Verzonden: woensdag 1 november 2006 11:04
Aan: RPG programming on the AS400 / iSeries
Onderwerp: RE: 8s0 field into a date using SQL


Dont you want to just do a Move into a field defined as a date field then do 
SUBDUR?

-----Oorspronkelijk bericht-----
Van: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Namens
mcrangle@xxxxxxxxxxxxxxxxxxxx
Verzonden: woensdag 1 november 2006 10:59
Aan: rpg400-l@xxxxxxxxxxxx
Onderwerp: 8s0 field into a date using SQL



I have a 8s0 field (FAILDT), with data stored as 20061231.

I want to convert to a date field, then select records where FAILDT <=
CURRENT DATE - 5 Years.

Obviously missing something in this statement...

SELECT fltitm,
       substr(faildt,1,4) || '/' ||
       substr(faildt,5,2) || '/' ||
       substr(faildt,7,2) as date
       from faultlog WHERE DATE(substr(faildt,1,4) || '/' ||
                                substr(faildt,5,2) || '/' ||
                                substr(faildt,7,2)) <= CURRENT DATE - 5
years

Once this is cracked I can hopefully get my boss to convert to SQL and ILE
from rpg400....

Thanks

Martin