Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 26th, 2006, 09:26 AM
Authorized User
 
Join Date: Oct 2006
Location: London, , United Kingdom.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default convert nvarchar to money

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.
Reply With Quote
  #2 (permalink)  
Old October 26th, 2006, 01:06 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

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
Reply With Quote
  #3 (permalink)  
Old October 26th, 2006, 06:37 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert from nvarchar(25) to smalldatetime aspless SQL Language 6 September 14th, 2007 09:21 AM
convert data from nvarchar to Date Time rahulgzb SQL Server 2000 4 September 7th, 2007 09:45 PM
change nvarchar to varchar vincentc SQL Server 2000 3 May 24th, 2005 10:56 PM
Convert int to nvarchar bekim SQL Language 1 August 12th, 2004 06:33 AM
retrieve numerice value from nvarchar mateenmohd SQL Server 2000 6 July 21st, 2003 03:52 AM



All times are GMT -4. The time now is 10:50 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.