select to 1 decimal place - 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)

select to 1 decimal place

Author

Topic

missMac
Posting Yak Master

124 Posts

Posted - 2008-12-29 : 19:03:47

Hello,
I have a column of numeric(18,6)

But in presenting to the user, how do i select to 0 decimal places

ie instead of 344.2342
I want simply 344

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-29 : 19:04:58

You could convert it to an integer data type or use the round function, but really you should format it in your application and not in SQL. Your question is a presentation issue and therefore should be handled by your presentation layer, which is your application.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-29 : 22:57:04

SELECT CONVERT(INT,344.2342)

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 00:28:03

are you just looking at getting integer part alone or you want to round the result also. if yes, then you can use ROUND(344.2342,0) also

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-30 : 00:30:54

try this also,to get integer part
select substring( '344.2342',1,charindex('.','344.2342',0)-1)

select left('344.2342',charindex('.','344.2342',0)-1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 00:42:49

quote:
Originally posted by bklr

try this also,to get integer part
select substring( '344.2342',1,charindex('.','344.2342',0)-1)

select left('344.2342',charindex('.','344.2342',0)-1)

Only problem in above is that the return type will be varchar

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-30 : 00:48:10

quote:
Originally posted by visakh16

quote:
Originally posted by bklr

try this also,to get integer part
select substring( '344.2342',1,charindex('.','344.2342',0)-1)

select left('344.2342',charindex('.','344.2342',0)-1)

Only problem in above is that the return type will be varchar

ya we can convert r cast it into int
just i gave the query to do by using substring, left also sample one

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 00:59:12

quote:
Originally posted by bklr

quote:
Originally posted by visakh16

quote:
Originally posted by bklr

try this also,to get integer part
select substring( '344.2342',1,charindex('.','344.2342',0)-1)

select left('344.2342',charindex('.','344.2342',0)-1)

Only problem in above is that the return type will be varchar

ya we can convert r cast it into int
just i gave the query to do by using substring, left also sample one

but it will simply cause an extra casting if you're doing this at places where you perform calculations.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-30 : 01:04:10

Do not use SUBSTRING or the LEFT functions to achieve this. It is pointless to do code like that for integer data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

missMac
Posting Yak Master

124 Posts

Posted - 2008-12-31 : 02:41:42

quote:
Originally posted by tkizer

Do not use SUBSTRING or the LEFT functions to achieve this. It is pointless to do code like that for integer data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

so whats your suggestion ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 02:43:23

Use ROUND() or cast it to int

missMac
Posting Yak Master

124 Posts

Posted - 2008-12-31 : 03:24:16

round doesnt work but cast does thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 03:25:27

quote:
Originally posted by missMac

round doesnt work but cast does thanks

why round doesnt work? whats the error?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-31 : 03:28:05

i think round(344.3423,0) will give the output as 344.000

i think her o/p is 344
so use cast or convert functions

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-31 : 12:31:43

Just convert it to int as I mentioned in the very first reply of this thread.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Thiyagu_04
Starting Member

37 Posts

Posted - 2009-01-01 : 02:46:51

Eg
value =123.6564

suppose if u want 124 as a result u can use
select round(cast(123.6564 as float),0)

suppose if u want 123 as a result u can use
select cast(123.65645644 as int)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources