RE: Converting Integer to Dates

Cast integers to character then cast it to date. For example of YYYYMMDD
integer and date format of MM-DD-YYYY, something like this ought to work:

Date(Substr(Char(field),5,2) || '-' || substr(char(field),7,2) || '-' ||
substr(char(field),1,4))

Elvis

2007 System i Fall Technical Conference | Orlando | November 4-7
Celebrating 10-Years of SQL Performance Excellence on IBM i5/OS and OS/400

-----Original Message-----
Subject: Converting Integer to Dates

Hi All,

Is there an easy way in the SQL interpreter to convert an integer to a
date? The original programmers thought it was cool to dump all
date/time fields into int 8/4 which sucks when you're trying to do
calculations on them (they also like to use a 25h clock, but that's a
whole other can of worms).

I tried a cast([field] to date), but the interpreter didn't like that.
I'd prefer to have the SQL server return the value without having to
resort to additional code in my RPG program. As I mentioned, the field
are set up as YYYYMMDD (8int) and times are set to HHMM (4int - no SS in
our tables!).

Thanks,

Brian Piotrowski