-2

I have a sales table where the SalesNo was a happy int. for years. Then I thought I would like to support split transactions. In this instance the salesNo should be 123.1 and 123.2 for example for a split transaction.

So I converted the SQL int to a decimal (16.1) and away we go after some coding. However, SQL Server insists on "appending" a .0 for SaleNo's that are just 123 storing it as 123.0. That causes issues I would rather not code around.

Can I make SQL Server not behave this way?

Dale K
  • 25,246
  • 15
  • 42
  • 71
id10t
  • 11
  • 1
  • 5
  • 2
    What issues does it cause? 123 or 123.0 are both different ways of rendering the same number. – ProgrammingLlama Jun 08 '22 at 03:33
  • 2
    A trailing zero does not change a number? You're thinking about it as a string... – Dale K Jun 08 '22 at 03:33
  • Does this answer your question? [Remove trailing zeros from decimal in SQL Server](https://stackoverflow.com/questions/2938296/remove-trailing-zeros-from-decimal-in-sql-server) – gunr2171 Jun 08 '22 at 03:35
  • Can you share the code which sends data to the database? I would recommend to have nvarchar as the type of column – Chetan Jun 08 '22 at 03:38
  • 4
    A number is a number in SQL Server, it has no format. What you are looking at is *your softwares's* way of displaying it (such as SSMS) – Charlieface Jun 08 '22 at 03:45
  • 2
    You should use two separate columns. Store the `123` in one column, as you always have, and store the `1` or `2` in a separate, new column. That new column can be nullable and would be null for existing records. You can then use some simple logic to display the combined value in your application. – John Jun 08 '22 at 03:50

1 Answers1

0

Basically you want to apply formatting of the way, you want to display it. In RDBMS, data is stored with respect to datatype. When you have datatype as decimal(16,1), decimal places are filled, even though you don't have need for decimal places.

You need to better handle these things in the UI layer. If you want to do through TSQL, you can do as below:

DECLARE @table table(val decimal(16,1))

insert into @table values(123.2),(123.0);

select case when val = cast(val as int) then format(val, '#')  else format(val,'#.#') end  
from @table

123.2
123

Dale K
  • 25,246
  • 15
  • 42
  • 71
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
  • Thanks guys. I will take your suggestions under consideration. When I "decided" do this I thought, you better not. :-) – id10t Jun 08 '22 at 11:29