SQL Server money constant syntax

Frediano Ziglio 21 Reputation points
2022-01-24T08:52:06.44+00:00

Hi,
in the page https://learn.microsoft.com/en-us/sql/t-sql/data-types/constants-transact-sql?view=sql-server-ver15 it's specified that

--------------------

SQL Server does not enforce any kind of grouping rules such as inserting a comma (,) every three digits in strings that represent money.

Commas are ignored anywhere in the specified money literal.

--------------------

I don't use SQL Server on a daily base but I never used such syntax with commas, so I tried with an instance a simple query like "SELECT $123,4" and, as my expectation, it returns 2 columns, not one. So I suspect commas are not supported and so the notes about grouping and commas being ignored are wrong in the documentation.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,690 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 117.1K Reputation points MVP
    2022-01-24T23:14:50.473+00:00

    I agree with Freddy. The Docs page is absolutely in error. Commas are ignored anywhere in the specified money literal. is plain wrong. You cannot have commas at all in a money literal. You can have commas in a string that you convert to money, but that's something else.

    In the upper right of the page, there is a pen icon that will take you to the Github repro for the Docs. If you are brave you can edit the page directly and submit a pull request. It may be better to submit an issue and let the writers work it out. (If you submit a pull request, it will be reviewed.)

    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Viorel 119.9K Reputation points
    2022-01-24T09:06:16.517+00:00

    Probably "every three digits in strings" means string constants, for example:

    SELECT $1000 + '$123,4'
    SELECT cast('$123,4' as money)

    1 person found this answer helpful.

  2. Tom Phillips 17,741 Reputation points
    2022-01-24T14:08:37.467+00:00

    SELECT $123,4 is 2 columns.

    That quote is referring to thousands separator in money data types.

    SELECT CAST('$123,456.78' AS MONEY)

    1 person found this answer helpful.

  3. Olaf Helper 46,041 Reputation points
    2022-01-24T11:11:04.607+00:00

    The conversion works if you do it the right way:

    select CONVERT(money, '$ 123.4');
    select CONVERT(money, '€ 123.4');
    select CONVERT(money, '£ 123.4');
    select CONVERT(money, '¥ 123.4');
    

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.