Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181

    Unanswered: number conversion to euro

    Hi folks,
    I want to convert a number eg 25 to 0.25 (ie in Euros) in a sql script I'm writing.
    I was thinking of using
    select convert(value,'ZZZ') from xxx;
    but have no idea what ZZZ should be or even if I'm on the right track.
    Anybody any ideas?
    Rgs,
    Breen.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: number conversion to euro

    I don't follow. Are you trying to convert cents to Euros (just divide by 100), or convert from one currency to another? The Oracle CONVERT function is for converting text between character sets.

  3. #3
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi Andrew,
    Thanks for your prompt reply.
    I'm obviously way off with convert().
    We have values of 8,25,250 etc in a table and I would like to return 0.08,0.25,2.50 etc.
    Again thanks for your help,
    Breen.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Simplest way is:

    SELECT cents_value/100 AS euro_value FROM table;

    If you want to be fancy, you could create a function:

    CREATE OR REPLACE FUNCTION cents_to_euros( p_cents INTEGER ) RETURN NUMBER IS
    BEGIN
    RETURN p_cents / 100 ;
    END;
    /

    Then:

    SELECT cents_to_euros( cents_value ) AS euro_value FROM table;

  5. #5
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Again, thanks for your reply Andrew.

    My basic select is:
    select totaldebit from table;
    returns:
    8
    10
    25
    250

    If I use:
    select totaldebit/100 from table;
    I get:
    .08
    .1
    .25
    2.5

    Even if I use your function
    SELECT cents_to_euros(totaldebit) AS euro_value FROM table
    I will get:
    .08
    .1
    .25
    2.5

    But what I'm looking for is the SQL to return values as follows:
    0.08
    0.10
    0.25
    2.50

    Again I thank you for any replies,
    Breen.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This is a display formatting issue. If you are using SQL Plus:

    COLUMN euro_value FORMAT 999.99

    (put as many 9's before the decimal point as necessary for the largest values to be displayed).

    - Tony Andrews

  7. #7
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Thanking your kind self Andrew.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Correction: to get 0.01 instead of .01 use the format masK:

    COLUMN euro_value FORMAT 990.99

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •