August 5, 2009 at 6:56 am
Hi there,
I have the following SQL Syntax -
SELECT DISTINCTSALE.Reference AS 'mhs Reference',
SALE.CustomerOrderRef AS 'arcHouse Reference',
CUST.Shortname,
TOT.ProductsCharges AS 'Net Total',
CONVERT (VARCHAR(10),TOT.TaxTotal)AS 'Tax',
TOT.ProductsCharges + TOT.TaxTotal AS 'Gross Total',
UP.CreatedDate,
UP.CreatedTime
FROM dbo.[Updates]AS UP
INNER JOIN
dbo.[SalesOrders] AS SALE
ON UP.TableRecordUID = SALE.UID
INNER JOIN
dbo.[Customers] AS CUST
ON SALE.CustomerID = CUST.UID
INNER JOIN
dbo.[SalesOrdersTotals] AS TOT
ON SALE.UID = TOT.SalesOrderID
WHERE UP.UpdatedText = 'Fully Invoiced'
ANDUP.SystemFormID = '45'
ANDUP.CreatedDate >= '27/07/2009'
AND CUST.ParentCustomerID = '41'
--AND TOT.TaxTotal <> '0.00'
ORDER BY UP.CreatedDate,
UP.CreatedTime
When I run this I get 175 results, which I am happy with.
When I unrim
AND TOT.TaxTotal <> '0.00'
I get the following error -
Disallowed implicit conversion from data type varchar to data type money, table 'CP98Data.dbo.SalesOrdersTotals', column 'TaxTotal'. Use the CONVERT function to run this query.
TOT.TaxTotal is held in the database as money.
Any ideas why I would be getting the error? Thanks
August 5, 2009 at 7:04 am
AND TOT.TaxTotal '0.00'
Take the quotes off
AND TOT.TaxTotal 0.00 and it should work.
August 5, 2009 at 7:08 am
So easy when you know how.
Thanks so much.
August 5, 2009 at 8:00 am
By way of explanation, it appears to be trying to convert the money datatype to the varchar datatype to make the comparison. (Not convert the '0.00' to money.) However, the money datatype is essentially a numeric datatype that can be converted to a string containing currency symbols, or not. It wanted you to do an explicit conversion to say exactly what that string would be before comparing it to the '0.00' string.
By removing the quote, you just compared numbers to numbers. The additional .00 wasn't needed. Zero would have been fine all by itself.
What is puzzling here is that I can declare a variable as a money datatype, assign it a dollar value, and then compare it to a string like '0.00' without any problems on my system.
declare @sample table (moolah money)
insert into @sample
select 1 union all
select 2 union all
select 3
select convert(varchar(10),moolah) as Filthy_Lucre
from @sample
where moolah >= '2.00'
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply