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

Hi,

before you can use a the scalar function SUBSTR you have to convert yournumeric values into a character string by using the scalar function DIGITS.Also YYYY/MM/DD is not a valid character representation of a date. Validrepresentations are YYYY-MM-DD, MM/DD/YYYY, DD.MM.YYYY.Date is a reserved word for SQL and cannot be used as column name. Youeither 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 doublepipes 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 '-'concatsubstr(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 theoriginal fields in the physical files. By using a scalar function in youcase 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." (LesBrown)

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