Solved

CAST as money does not work for varchar in SQL 2000

Posted on 2011-03-16
5
453 Views
Last Modified: 2012-05-11
Hello experts:
I have the following SQL command issued from a C# Windows application.

SELECT count(distinct ACCOUNTNUMBER) FROM Extract WHERE (TransactionType = 'CASH_WD' OR (CAST(CASHOUTAMOUNTINSPLITDEPOSIT as money) > 0 and TransactionType = 'WITHDRAWAL' and cast(transactionamount as money) > '3000')) and TaxIdOrSSN = '043305700'

It works fine in SQL Server 2005 but it's failing in SQL Server 2000 with this message:
Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.


0
Comment
Question by:lancerxe
  • 3
  • 2
5 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 35151911
try this.
SELECT COUNT(DISTINCT ACCOUNTNUMBER) 
  FROM EXTRACT 
 WHERE (TransactionType = 'CASH_WD' 
         OR (CAST(CASHOUTAMOUNTINSPLITDEPOSIT AS MONEY) > 0 
             AND TransactionType = 'WITHDRAWAL' 
             AND CAST(transactionamount AS MONEY) > 3000)) 
       AND TaxIdOrSSN = '043305700'

Open in new window

0
 

Author Comment

by:lancerxe
ID: 35159751
I found the solution:

SELECT count(distinct ACCOUNTNUMBER) FROM Extract WHERE (TransactionType = 'CASH_WD' OR (CAST(CASHOUTAMOUNTINSPLITDEPOSIT as money) > 0 and TransactionType = 'WITHDRAWAL' and cast((transactionamount as money) > cast( '3000' as money))) and TaxIdOrSSN = '043305700'

Do I get points if I find it myself?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35159795
Did you try my post. You need not to convert a varchar to money again. You can directly compare by removing the quotes.
0
 

Author Comment

by:lancerxe
ID: 35160028
Sorry Sharath but I did not see any difference bewtween the original script l posted and yours.

Lance
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35160399
The difference is I removed the quotes around 3000. There is no need to take 3000 as varchar and then convert it to numerical. You can directly compare with 3000.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to "ASSIGN" a RecordID based on 1st character of a Field? 8 35
sql Audit table 3 59
CREATE DATABASE ENCRYPTION KEY 1 56
INSERT DATE FROM STRING COLUMN 18 49
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now