I've looked in Transact-SQL help and searched P2P but can't see that I can convert an nvarchar column to money type. My goal is, to take numbers and format them with a comma as thousands separator. The nvarchar column contains numbers that were converted to nvarchar for another reason, and now I want them back as numbers.

When I do this:
   case when columnx='.00' then '' else convert(money,columnx,1) end
I get an error saying that an implicit conversion is not allowed and I must use the convert function. ????
I'm using money,1 because I think that style allows for a comma as thousands separator, but I got it from Help in the context of changing FROM money TO nvarchar, the other way 'round from what I want to do.

Try this as a sample


declare @money money;
set @money=9999;
select CAST ( @money AS varchar (50 ) )Money;

or
select cast (9999 as varchar(30)) Your_C

Jaime E. Maccou

This will do it... be warned... numeric datatypes cannot hold commas... only character data types can... that's why the code below converts NVARCHAR to MONEY and then back again using the formatting operand of "1" in the convert.

DECLARE @TestNum NVARCHAR(16)
    SET @TestNum = N'1234567.89'

SELECT CONVERT(NVARCHAR(16),CAST(@TestNum AS MONEY),1)


Obviously, this is just test code and you need to do something more like the following...


 SELECT CONVERT(NVARCHAR(16),CAST(yourcolumnname AS MONEY),1) AS somename
FROM yourtablename

--Jeff Moden