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.