RE: 8s0 field into a date using SQL

Would love to :-/

But we are being pushed down the SQL route and I want to prove that I can
code in both, with a little help from all you good people on this list...



                                                                           
             "Raby, Steve"                                                 
             <agnictsr@aerogrou                                            
             nd.nl>                                                     To 
             Sent by:                   "RPG programming on the AS400 /    
             rpg400-l-bounces@m         iSeries" <rpg400-l@xxxxxxxxxxxx>   
             idrange.com                                                cc 
                                                                           
                                                                   Subject 
             01/11/2006 10:03           RE: 8s0 field into a date using    
                                        SQL                                
                                                                           
             Please respond to                                             
             RPG programming on                                            
                the AS400 /                                                
                  iSeries                                                  
             <rpg400-l@midrange                                            
                   .com>                                                   
                                                                           
                                                                           




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

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.