Re: 8s0 field into a date using SQL

["Gene Burns" <burns.gene@xxxxxxxxx>]

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.