Casting a String to 3 decimal places

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

General SQL Server Forums

New to SQL Server Programming

Casting a String to 3 decimal places

Author

Topic

ashgupta
Starting Member

17 Posts

Posted - 2009-02-26 : 23:04:02

Hi experts ,
I have a table with [rate] nvarchar (255) ,
which is used to store values.
I known the field type should not be nvarchar
however i want to get the values frm the field up to 3 decimal places without rounding off the last digit

eg 1111.999999 --> should be -->1111.999
or 11.99---> 11.990
any help welcome.

my query
select rate, convert(nvarchar(255), cast(Rate AS decimal(10,3)))

however this is rounding off the 4th decimal place

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-26 : 23:12:02

try like this

declare @t nvarchar(255)
select @t = 111.99987
select PARSENAME(@t,2)+'.'+LEFT(PARSENAME(@t,1),3)

ashgupta
Starting Member

17 Posts

Posted - 2009-02-26 : 23:24:39

thx,
however , this does work for
declare @t nvarchar(255)
select @t = 111
select PARSENAME(@t,2)+'.'+LEFT(PARSENAME(@t,1),3)

result to Null
expected result - 111.000

matty
Posting Yak Master

161 Posts

Posted - 2009-02-26 : 23:58:17

DECLARE @t NVARCHAR(255)
SELECT @t = 111.999999
SELECT CONVERT(DECIMAL(10,3),SUBSTRING(@t,1,CHARINDEX('.',@t) + 3))

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-26 : 23:58:47

Try this once,

declare @t nvarchar(255)
select @t = 1111

select case when @t like '%.%' then PARSENAME(@t,2)+'.'+LEFT(PARSENAME(@t,1),3) else cast(@t as decimal(18,3)) end as 'Value'

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-27 : 00:08:06

then try this

declare @t nvarchar(255)
select @t = 111.345345
IF CHARINDEX('.',@t) = 0
SELECT LEFT(PARSENAME(@t,1),3)+'.'+'000'
ELSE
SELECT PARSENAME(@t,2)+'.'+LEFT(PARSENAME(@t,1),3)

matty
Posting Yak Master

161 Posts

Posted - 2009-02-27 : 00:08:22

Edit:

SELECT CONVERT(DECIMAL(10,3),CASE WHEN CHARINDEX('.',@t) > 0 THEN SUBSTRING(@t,1,CHARINDEX('.',@t) + 3) ELSE @t END)

quote:
Originally posted by matty

DECLARE @t NVARCHAR(255)
SELECT @t = 111.999999
SELECT CONVERT(DECIMAL(10,3),SUBSTRING(@t,1,CHARINDEX('.',@t) + 3))

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-27 : 00:12:42

quote:
Originally posted by ashgupta

thx,
however , this does work for
declare @t nvarchar(255)
select @t = 111
select PARSENAME(@t,2)+'.'+LEFT(PARSENAME(@t,1),3)

result to Null
expected result - 111.000

try this
declare @t nvarchar(255)
select @t = 111
select ISNULL(PARSENAME(@t,2)+'.'+LEFT(PARSENAME(@t,1),3),@t+'.000')
select COALESCE(PARSENAME(@t,2)+'.'+LEFT(PARSENAME(@t,1),3),@t+'.000')
select @t = 111.56456
select ISNULL(PARSENAME(@t,2)+'.'+LEFT(PARSENAME(@t,1),3),@t+'.000')
select COALESCE(PARSENAME(@t,2)+'.'+LEFT(PARSENAME(@t,1),3),@t+'.000')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources