10
SELECT ROUND(123.4567, 2)` gives me `123.4600`

But I need 123.46.

Data type of field is money.

Solution:

<%# DataBinder.Eval(Container.DataItem, "FieldName","{0:0.00}") %>
abatishchev
  • 98,240
  • 88
  • 296
  • 433
IsmailS
  • 10,797
  • 21
  • 82
  • 134

5 Answers5

23
SELECT CAST(ROUND(123.4567, 2) AS MONEY)

Will do what you are after

Neil Knight
  • 47,437
  • 25
  • 129
  • 188
  • This works, but when I assign the value to a field in Repeater on my .aspx page, it again show two trailing zeros. I'm using Linq to sql. – IsmailS Aug 18 '10 at 08:02
  • You could use a ToString() in your repeater specifying the format to how you want it. – Neil Knight Aug 18 '10 at 08:06
  • +1 thanks for your answer. Your answer is also right looking at my question. But @abatishchev tried to solve the bigger problem instead of just answering my question. That's why, I've accepted his answer. – IsmailS Aug 18 '10 at 08:10
12

If applicable, format on the view layer, not on the data layer, i.e. read all the data and truncate it later (such as in C# client)

abatishchev
  • 98,240
  • 88
  • 296
  • 433
  • +1. Binding it like this in repeater worked `<%#DataBinder.Eval(Container.DataItem, "FieldName","{0:0.00}")%>`. Thanks for directing me to solve the actual problem. – IsmailS Aug 18 '10 at 08:06
  • @Ismail: Glad it helped! :) P.S. I had the same problems - http://stackoverflow.com/questions/1308259/rounding-down-decimal14-3-to-third-decimal-digit-in-sql-2008 http://stackoverflow.com/questions/2938296/remove-trailing-zeros-from-decimal-in-sql-server – abatishchev Aug 18 '10 at 10:34
  • But this would not work in edit components like EvExpress grid. Edit forms are broken by that. – f470071 Oct 02 '15 at 11:44
4

@IsmailS - To me, the larger picture is to let the data server format as much as possible, especially when it's something as simple as an inline cast. It yields less clutter in your other code. For example, casting Price, a money field:

select CAST(Price as numeric(17,2)) Price from PriceTable

YMMV - It's my personal experience.

Madhukar Mohanraju
  • 2,793
  • 11
  • 28
Christopher Huff
  • 51
  • 3
2
SELECT FORMAT(123.4567,'N2')

It will Help You Out

Abhishek Singh
  • 101
  • 5
1
SELECT ROUND(123.4567, 2,1)

See http://msdn.microsoft.com/en-us/library/ms175003(SQL.90).aspx

Jacob
  • 77,566
  • 24
  • 149
  • 228
Anirudha
  • 19
  • 1