my money cast no longer works in sql 2005

Hi

In a SQL 2000 stored proc I have the following statement to return a formatted price without the decimal place and cents

SELECT @.strPrice = '$' + convert(varchar(12),cast(@.price as money(12,2)),1)

This fails syntax checking in SQL 2005 (CAST or CONVERT: invalid attributes specified for type 'money')
By reading this forum I can see that I can use this instead:
select '$' + parsename(convert(varchar,convert(money,@.price),1),2)


I'm wondering why it doesn't work any more in SQL 2005 - are these differences documented anywhere ?

thanks
Bruce

It is because you are specifying precision and scale for money (money(12,2)) which is incorrect/invalid. Older versions allowed such syntaxes due to bug in parser and this has been corrected now. Do:

SELECT @.strPrice = '$' + convert(varchar(12),cast(@.price as money),1)

|||> This fails syntax checking in SQL 2005 (CAST or CONVERT: invalid
> attributes specified for type 'money') Where is there any documentation that states that MONEY has precision or scale? The SQL Server 2005 error message is correct. SQL Server 2000 was a little bit more lenient and just ignored the attributes, but don't blame the tool that you misused. :-) I would MUCH RATHER have this kind of thing break on me, than just ignore my specifications and lull me into believing that it is working correctly, when it is in fact not. DECLARE @.PRICE MONEY SET @.Price = 476.2354 SELECT CAST(@.Price AS MONEY(12,2)) Now, try instead: SELECT CONVERT(MONEY(12,2), @.Price) Hey, they got the error message right for CONVERT, but not for CAST. Server: Msg 291, Level 16, State 1, Line 3 CAST or CONVERT: invalid attributes specified for type 'money'
> By reading this forum I can see that I can use this instead:
> select '$' + parsename(convert(varchar,convert(money,@.price),1),2) Why do you need to convert to money? What is the original data type? If it is decimal or numeric, then the following will work fine: SELECT '$' + RTRIM(CONVERT(DECIMAL(12,2), @.price)) Or, just SELECT @.price And let the client/presentation tier add a dollar sign and round to the correct number of decimal places. I've actually never ended up using MONEY or SMALLMONEY in any database application I've built. Several reasons are listed here: http://www.aspfaq.com/2503
> I'm wondering why it doesn't work any more in SQL 2005 - are these
> differences documented anywhere ? No, that's the problem with having a loose syntax checker in previous versions, that allowed invalid things to work. I'm not even sure that Microsoft is aware of all of these obscure things that just happened to work in previous versions. If Microsoft were to document all of the undocumented behaviors that change, where should they put it? How would people find it? Who is going to tell them about all of the undocumented things that we all know about, and should be documented? There are some other examples, though I know they are aware of these, e.g. CREATE VIEW ... AS SELECT TOP 100 PERCENT ... ... ORDER BY This still passes the syntax checker, and you can still create the view, but now the lazy "SELECT * FROM viewname" no longer guarantees . Since the behavior people relied on was undocumented, . Another is CREATE TABLE dbo.foo ( col1 INT, col2 CHAR(1), ) This passes the syntax checker in both versions and works, even though there is a syntax error (dangling comma). A