RE: 8s0 field into a date using SQL -- RPG400-L

Let us see your new statement.  If you weren't expecting anything earlier than 
2001-11-01, then I'd say you need to use
"Faildt >= current_date - 5 years" not the "Faildt <= current_date - 5 years" 
you originally posted.

Note that you'd get better performance by using

Select fltitm, faildt

From faultlog

Where faildt >= year(current_date - 5 years) * 10000 
                 + month(current_date - 5 years) * 100  
                 + day(current_date - 5 years) 


By performing operations on the faildt column in the WHERE clause, you force 
the DB to do a table scan. My version on the other hand, will make use of any 
indexes over faildt.


HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx 
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of 
mcrangle@xxxxxxxxxxxxxxxxxxxx
Sent: Wednesday, November 01, 2006 5:54 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: 8s0 field into a date using SQL



Cheers Birgitta,

Thats worked sort of, below is a snap shot of data, but I 
wasnt expecting
any dates earlier than 2001-11-01;

FLTITM     MYDATE
                 1993-02-20
V906195    1996-06-18
                 1994-11-10
                 1996-08-05
V027902    1992-07-02
                 1995-12-07
                 1993-06-30
                 1994-02-23
                 1997-10-07

So close yet so far...



                                                              
             
             "HauserBirgitta"                                 
             
             <Hauser@SSS-Softwa                               
             
             re.de>                                           
          To 
             Sent by:                   "RPG programming on 
the AS400 /    
             rpg400-l-bounces+m         iSeries" 
<rpg400-l@xxxxxxxxxxxx>   
             crangle=cinetic-la                               
          cc 
             ndis.co.uk@midrang                               
             
             e.com                                            
     Subject 
                                        Re: 8s0 field into a 
date using    
                                        SQL                   
             
             01/11/2006 10:20                                 
             
                                                              
             
                                                              
             
             Please respond to                                
             
             RPG programming on                               
             
                the AS400 /                                   
             
                  iSeries                                     
             
             <rpg400-l@midrange                               
             
                   .com>                                      
             
                                                              
             
                                                              
             




Hi,

before you can use a the scalar function SUBSTR you have to 
convert your
numeric values into a character string by using the scalar 
function DIGITS.
Also YYYY/MM/DD is not a valid character representation of a 
date. Valid
representations are YYYY-MM-DD, MM/DD/YYYY, DD.MM.YYYY.
Date is a reserved word for SQL and cannot be used as column name. You
either have to use a different name or to embedd date in double quotes
"date". If you use double quotes, field names are case sensitive i.e.
"Date"
<> "date" <> "DATE".
Instead of the double pipes you should prefer concat, because 
the double
pipes are not international and cannot be used with all languages.

 SELECT fltitm,
       substr(digits(faildt), 1, 4) concat '-' concat
       substr(digits(faildt), 5, 2) concat '-' concat
       substr(digits(faildt), 7, 2) as Mydate
       from faultlog WHERE DATE(substr(digits(faildt),1,4) concat '-'
concat
                                substr(digits(faildt), 5, 2) 
concat '-'
concat
                                substr(digits(faildt), 7, 2)) <=
CURRENT_DATE - 5 years

Last comment:
For the query above a table scann will be used, because access paths
(indexes or DDS described logical files) are only used when 
build over the
original fields in the physical files. By using a scalar 
function in you
case DATE(), SUBSTR(), DIGITS() an access path over faildt 
cannot be used.

Mit freunlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the 
stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)




----- Original Message -----
From: <mcrangle@xxxxxxxxxxxxxxxxxxxx>
To: <rpg400-l@xxxxxxxxxxxx>
Sent: Wednesday, November 01, 2006 10:59
Subject: 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.




-- 
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.