Re: Converting alpha to numeric in Query

["Barry L. Kline" <blkline@xxxxxxxxxxxxx>]

Dan Bale wrote:
>
> Wrong way.  That converts a numeric to alpha.  Albert wants the other way.
> AFAIK, can't be done.
>
> - Dan Bale

Actually, it can be done:

1) Create a UDF that calls an ILE/RPG function to do the job or

2) use the MICROSECOND SQL function... To do the latter, you need to
create a
string that looks like an AS/400 timestamp:

e.g.  0001-01-01-01.01.01.000000 by doing this:

select microsecond('0001-01-01-01.01.01.'||your_field) from file.

This assumes a 6 digit character field.  If your field is shorter, add
corresponding zeroes to after the last decimal point in the constant
field.  So, for a 3 digit field:

select microsecond('0001-01-01-01.01.01.000'||your_field) from file.

microsecond returns a numeric value.

Barry