Truncate digits - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Truncate digits

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-25 : 04:28:49

Hi,

Can I truncate digit from a number by round function...

For example 1.66666
can i make it 1.67 though round function

Vabhav T

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-25 : 04:32:36

select round(1.66666,2)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-25 : 04:34:13

I tried it but it is not truncating zeros after 67, i dont need it...

Vabhav T

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-25 : 04:35:19

use convert() to convert it to decimal or numeric data type with the required scale & prec

Or just format it accordingly to your required format in your front end application

KH
[spoiler]Time is always against us[/spoiler]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-25 : 04:35:33

Try this

select cast (round(1.66666,2) as float)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-25 : 04:40:06

senthil's suggestion is better because i dont want zeros only
but by scale and precision i may loose the data if mismatch occurs in rounding and scale-precision

Vabhav T

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-25 : 04:42:37

quote:
Originally posted by vaibhavktiwari83

senthil's suggestion is better because i dont want zeros only
but by scale and precision i may loose the data if mismatch occurs in rounding and scale-precision

Vabhav T

Fine then go ahead!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 04:48:12

select cast(1.66666 as decimal(12,2))

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-25 : 04:53:51

quote:
Originally posted by vaibhavktiwari83

senthil's suggestion is better because i dont want zeros only
but by scale and precision i may loose the data if mismatch occurs in rounding and scale-precision

Vabhav T

select cast (round(1.66666,2) as float)
will give you 1.6699999999999999 and this is what you want ?

take note that float is a approximate value

KH
[spoiler]Time is always against us[/spoiler]

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-25 : 05:03:04

WHERE You are executing this query because it is giving me result 1.67

Vabhav T

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-25 : 05:05:13

quote:
Originally posted by khtan

quote:
Originally posted by vaibhavktiwari83

senthil's suggestion is better because i dont want zeros only
but by scale and precision i may loose the data if mismatch occurs in rounding and scale-precision

Vabhav T

select cast (round(1.66666,2) as float)
will give you 1.6699999999999999 and this is what you want ?

take note that float is a approximate value

KH
[spoiler]Time is always against us[/spoiler]

select cast (round(1.66666,2) as float)

It won't result 1.6699999999999999

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-25 : 06:06:16

strange . . . must be due to the client presentation

select cast ( 1.67 as float)

running above in SSMS will give 1.67, in QA it return as 1.6699999999999999

KH
[spoiler]Time is always against us[/spoiler]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-25 : 06:08:26

quote:
Originally posted by khtan

strange . . . must be due to the client presentation

select cast ( 1.67 as float)

running above in SSMS will give 1.67, in QA it return as 1.6699999999999999

KH
[spoiler]Time is always against us[/spoiler]

Cool,

Then What we can thrust either SSMS or QA?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources