Re: 8s0 field into a date using SQL

We have our dates split into three fields, so I created a service program
and SQL Function to return a date field.  You could modify this easily to do
the same with an 8s0 field.

http://code.midrange.com/index.php?id=d6059396d0

Gene Burns

On 11/1/06, mcrangle@xxxxxxxxxxxxxxxxxxxx <mcrangle@xxxxxxxxxxxxxxxxxxxx>
wrote:

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.