Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I have a database with a column of VARCHAR2 type keeping a value like: 398566,569885

What I need is to cast this number to float and then to make it looking like: 398566,56 Casting it to float is not so important, if It's possible to trim the numbers it will be also ok.

What I have tried is:

select
cast (p1.VALUE as float) as Total
from Workflow p
LEFT JOIN PA_PARAMETER p1 on p1.ID=p.ID AND p1.NAME = 'Total'
WHERE p.TYPE = 'Marketing'
Is there a way to trim these numbers or to round it somehow?
                As far as I know, SQL Server doesn't have a VARCHAR2 datatype (that would be Oracle) ....
– marc_s
                Nov 2, 2012 at 9:14
                seems to be working but it's returning +1 in the results. For exampe 5698.6548 is casted to 5698.66
– Slim
                Nov 2, 2012 at 9:03
                @podiluska I'm 99% sure SQL Server cannot round 5698.6548 to 5698.66. Only way to do it I can imagine is to do bankers' rounding. C# round work as banker's rounding by default, though
– Roman Pekar
                Nov 2, 2012 at 9:22
But then you'll receive 398566.57 because of rounding

To get 398566.56 you have to use round function with specified third parameter - http://msdn.microsoft.com/en-us/library/ms175003.aspx (When a value other than 0 is specified, numeric_expression is truncated.)
select cast(round(cast(@a as decimal(29, 10)), 2, 1) as decimal(29, 2))
From what I can gather, you only want to reformat a VARCHAR column containing some (European) decimal text to 2 places.
select
left(p1.value, charindex(',', p1.value + ',')+2) as Total
from Workflow p
LEFT JOIN PA_PARAMETER p1 on p1.ID=p.ID AND p1.NAME = 'Total'
WHERE p.TYPE = 'Marketing';
And if you consistently want the 2 decimal places, then
select
left(p1.value + ',00', charindex(',', p1.value + ',')+2) as Total
from Workflow p
LEFT JOIN PA_PARAMETER p1 on p1.ID=p.ID AND p1.NAME = 'Total'
WHERE p.TYPE = 'Marketing';
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.