Search code examples
sqlsql-servercurrencydata-conversion

Converting currency values - eurocents to euros with rounding

the application exports data to my DWH in euro cents. I need to convert these values to euros with rounding up. Euro cent data format is int

Here is a sample of the data in the table:

  CustID   Money   Date
  ===========================
  1        148180  2021-02-10
  2        520     2021-02-11
  3        50      2021-02-11
  4        25      2021-02-12

Well, the column Money that we know is in eurocents, which means that rounding to the correct value will be for CustID:

  1 - 1482 
  2 - 5,2 
  3 - 0.5 
  4 - 0.3

The point is that when the value in the Money column is only Eurocents, as in the case of CustID 3 or 4, I must also show this value, not just the number without the decimal point. So I need to set the conversion to this column to two decimal places. Do you have any suggestions?

EDIT

I tried something like this, but the result is 1,481 with rounding down and without two decimal numbers.

SELECT CONVERT(varchar, CAST(148180/100 AS money), 1)

Solution

  • You get from euro cents to euros by dividing by 100. Use 100.0 in order to avoid integer division in SQL Server.

    You round up to the next integer number with CEILING.

    Now to the display format. You want two decimal places. A number, however, does not have a particular number of decimal places. The number 1.2 is the same as 1.20 and still the same as 1.20000 in a SQL Server database.

    If you want to display a number in a certain format, you need a function to convert the number into a string with the desired decimal places, the decimal character matching your culture (comma or point), etc. The function for this is FORMAT.

    select custid, format(ceiling(money / 100.0), 'N2') as euros
from mytable;

    The format 'N2' is just an example. You can of course use a different format. Read about this here: https://learn.microsoft.com/de-de/sql/t-sql/functions/format-transact-sql?view=sql-server-ver15 and here: https://learn.microsoft.com/de-de/dotnet/standard/base-types/standard-numeric-format-strings.

    Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8ce85ce73e624d2b6bf9426b410be0fd