Good News Everybody!
The new search engine is LIVE!
Please report any problems to david (at) midrange.com.
|
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
midrange.com
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 SQLI have a 8s0 field (FAILDT), with data stored as 20061231. I want to convert to a date field, then select recordswhere 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 - 5years Once this is cracked I can hopefully get my boss to convertto SQL and ILEfrom rpg400.... Thanks Martin -- This is the RPG programming on the AS400 / iSeries(RPG400-L) mailing listTo 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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2026 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
