Query using DIGITS function question - IBM AS400

This is a discussion on Query using DIGITS function question - IBM AS400 ; I have a field that I am doing digits against that is was 6,0. I then substring it out to get 3 seperate fields MM, DD, YY. I then want to get these back as a numeric in query, any ...

+ Reply to Thread
Results 1 to 3 of 3

Thread: Query using DIGITS function question

  1. Query using DIGITS function question

    I have a field that I am doing digits against that is was 6,0. I then
    substring it out to get 3 seperate fields MM, DD, YY. I then want to
    get these back as a numeric in query, any ideas how I can do this.
    Thanks.


  2. Re: Query using DIGITS function question

    I presume you are separating them to sort/select by date perhaps because it
    is store as mmyyyy. So you want to reassemble them to do what? print them as
    mm/dd/yy? If that's the case them just concat them as month || '/' || day ||
    '/' || year as a 8 alpha field or some other variation. or are you wanting
    to reassemble for selection, then make it yy || mm || dd. To sort the date
    it doesn't have to be a numeric field. No you can not assemble them as a ne
    number in iSeries Query.

    Just guessing cause you don't provide much more info.

    Good Luck
    Bill

    "jacko" wrote in message
    news:1163018262.302932.277660@m73g2000cwd.googlegr oups.com...
    >I have a field that I am doing digits against that is was 6,0. I then
    > substring it out to get 3 seperate fields MM, DD, YY. I then want to
    > get these back as a numeric in query, any ideas how I can do this.
    > Thanks.
    >




  3. Re: Query using DIGITS function question

    jacko wrote:
    > I have a field that I am doing digits against that is was 6,0. I then
    > substring it out to get 3 seperate fields MM, DD, YY. I then want to
    > get these back as a numeric in query, any ideas how I can do this.
    > Thanks.


    I'll assume your 6,0 field contains dates in yymmdd format. You can
    make the necessary changes if it's in some other configuration. I'll
    also call the field date60.

    select cast(left(digits(date60),2) as dec(2,0)) the_year,
    cast(substr(digits(date60),3,2) as dec(2,0)) the_month,
    cast(right(digits(date60),2) as dec(2,0)) the_day
    from somelib/sometable


+ Reply to Thread