CODEWITHSUNDEEP
sqlsql-serversql-server-2000
JetJack
JetJack

Reputation: 988

How to convert into decimal column value

Using SQL Server 2000

table1 

id values (datatype is nvarchar)

001 12:10 
002 01:25
003 10:01
004 21:20

I want to convert the values column into float..

Tried Query

Select id, Cast(Replace(values, '':'', ''.'') as float) as values from table1

Output is showing like

001 12.1 'zero is not showing
002 01.25
003 10.01
004 21.2 'zero is not showing

Why zero is hiding..? 2.10 or 2.1 is same for the float datatype but user will get confuse 2.1 like 2 hour 1 minutes instead of 2 hours 10 minutes...

How to solve this problem

Expected Output

id values (datatype is float)

001 12.10 
002 01.25
003 10.01
004 21.20

Need SQL Query Help

Upvotes: 0

Views: 717

Answers (2)

Nalaka526
Nalaka526

Reputation: 11464

Try 

Select id, Cast(Replace(values, '':'', ''.'') as DECIMAl(10,2)) as values from table1

Upvotes: 0

Heinzi
Heinzi

Reputation: 172200

12.10 and 12.1 are both string representations of the same float (twelve plus one tenth). If you want the data type to be float you cannot specify its string representation. You can only do that when converting a float into a string, which would be a pointless exercise in your case (since you already have this value as a string).

Another data type instead of float, such as decimal, which has a fixed number of decimal places, might be a more appropriate choice for your task.

Upvotes: 2

Related Questions