0

I am using sqlquery function in R to extract data from SQL Server database and one of the columns the value is 10.00 for example when querying within SQL Server. When I use this sqlquery function in R, the column is showing up as just 10, and I am needing to have it show as 10.00 within the dataset in R. The column in SQL Server is a decimal(10,2).

This is what I have for example:

library(RODBC)

data <- sqlQuery(connection, "SELECT amount from table1")

This will result in 

amount
10

How would I be able to have this dataset show 10.00 for this column such as:

amount
10.00

It seems to show the decimal places if it's anything other than .00. I'd like for it to also show .00 as well.

Parfait
  • 104,375
  • 17
  • 94
  • 125
jwalls91
  • 341
  • 1
  • 5
  • 14
  • 3
    Possible duplicate of [Formatting Decimal places in R](https://stackoverflow.com/questions/3443687/formatting-decimal-places-in-r) – IceCreamToucan May 14 '19 at 16:44
  • It would help to know why you would need this, as you are not losing precision when reading `10.00` and the number being converted to `10`. Is it just for a matter of *seeing* all the values in that `amount` column in a consistent way with other values with decimals? Does that column has numbers with non-zero decimal places? Do you need this to happen just on the `amount` column or on all similar columns read from the database? – mastropi May 15 '19 at 12:09
  • Yes, the file that I am generating is sent off to another party and they require a strict format with our columns, one being that any total amount columns needing to show the two trailing zeros. The amount column is the only column that this would apply to and it does not have non zero decimal places. With that being said though, they still require the file to show the 10.00. – jwalls91 May 15 '19 at 13:01
  • More specifically, I am needing to do this somehow within the dataset through the sqlquery function from RODBC. Outside of this, I know I can do it by using format(round(5, 2), nsmall = 2) for example. – jwalls91 May 15 '19 at 13:54

1 Answers1

0

I was able to find a way to get it to work, I created another variable to store a dataframe to create a new column and used 

Amount <- data.frame("Amount" = format(round(data$column,2), nsmall = 2))

Then I replaced the old column within the dataset with this new variable I created and now the trailing zeros show

data$Amount <- Amount$Amount
jwalls91
  • 341
  • 1
  • 5
  • 14