RE: SQL Newbie question

In SQL there is no native facility for mapping numeric date values into date
types, but you can do what you want with a fairly simple expression...

First, you need to turn the numeric value into a character string, in such a
way that you do not lose digits.  Use the DIGITS() finction for this.  Then
you substring out the bits you want...

Select substr(digits(datemdy),5,2) as dtyear,
         substr(digits(datemdy),1,2) as dtmonth,
         substr(digits(datemdy),3,2) ad dtday
* 
from freight join orderdtl on (join criteria) 
Order by 1,2

Probably a more robust answer is to use a UDF to translate your numeric
values into date values.  Then you can use SQLs built in date functions to
manipulate date element values (Year, Month, Day).

There have been recent postings of date conversion UDFs to the list.  Check
the archives for more details.

hth,

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of steema@xxxxxxxxxxxxx
Sent: Thursday, December 22, 2005 9:57 AM
To: RPG programming on the AS400 / iSeries
Cc: midrange-l@xxxxxxxxxxxx
Subject: SQL Newbie question


I need to take 2 files, one is the Frieght, and the other is Order detail,
which has the real amt paid for frt. To sort by year where the format is
mmddyy. I would like to do this in SQL. I am not sure how to flex out the
year and sort on it, how to total various warehouses and other codes.