0

I am getting wrong result while using FORMAT in my query .. I want to format the numbers into Indian currency I am following right approach but its not working

Here is my query

select distinct FORMAT(sum(netamount), 'en_IN') as amount
from syncbill 
where cancelled<>'Y' and year(curdate())=year(billdate)

my value is as netamount is 27227004 after formatting it should show 2,72,27,004 but its showing 27,227,004

I don't know where I am going wrong please guide me if anyone having knowledge here. Edited image

this is i am getting while running this query

    select distinct FORMAT(sum(netamount), 0, 'en_IN') as amount 
from syncbill
where cancelled<>'Y' and year(curdate())=year(billdate)

1 Answers1

2

This answer only applies to MySQL versions 5.5 and above. The locale parameter to FORMAT was introduced in that version.

You forgot to specify the number of decimal places to FORMAT. Try this:

select distinct FORMAT(sum(netamount), 0, 'en_IN') as amount 
from syncbill
where cancelled<>'Y' and year(curdate())=year(billdate)

For example (demo):

select  FORMAT(27227004, 0, 'en_IN')

Output

2,72,27,004
Nick
  • 138,499
  • 22
  • 57
  • 95
  • hey look i have edited the question i have updated a image like what error i am getting i am using mySql 5.1 and rezor sql editor –  Nov 19 '18 at 06:42
  • Unfortunately your version of MySQL does not support the `locale` parameter to the `FORMAT` function, it was only introduced in version 5.5. See the [manual](https://docs.oracle.com/cd/E19078-01/mysql/mysql-refman-5.1/functions.html#function_format) – Nick Nov 19 '18 at 08:05
  • oohh kk so thats the issue...so is there any alternative to that, i want to achieve that only in sql query end –  Nov 19 '18 at 08:13
  • I had a good look through the MySQL 5.1 manual and couldn't see any reference to locale formatting other than for dates. I think you're going to need to deal with it in your application. – Nick Nov 19 '18 at 08:14
  • @dheerajkumar Unfortunately I don't know Java. I suggest you post another question with a `java` tag, or searching google for `java currency format` seems to show up a few answers on SO. It's probably worth leaving this one as it might be useful to others, but you could delete it if you choose. – Nick Nov 19 '18 at 08:27
  • What is the use of 'en_IN' – Rahman Haroon Dec 30 '20 at 06:16
  • @RahmanHaroon it is a locale selector that controls the number formatting, `en_IN` corresponds to `English (India)`. See for example https://stackoverflow.com/q/3191664/9473764 – Nick Dec 30 '20 at 06:37
  • What will be the value by default..? If I pass FORMAT(27004, 4) like this (no en_IN) – Rahman Haroon Dec 30 '20 at 09:17
  • @RahmanHaroon you can try things like that for yourself on sites such as dbfiddle.uk (the site linked to for my demo). But if the locale is not specified, it defaults to `en_US`, so `FORMAT(27004, 4)` will output `27,000.0000` – Nick Dec 30 '20 at 09:40