karthik M (12/1/2012)

---

All,

I have recently faced the below scenario.
i.e to add comma as the thousand seperator

I have searched here and found the below query

select convert(varchar,cast(12345678918 as money),1)

how this query is adding ',' ?

even i tried different combination of numbers which start with 1, all of them adding ',' . But when i use other than 1, it didn't. how? what is the magic behind this '1' ?

money and smallmoney Styles
When expression is money or smallmoney, style can be one of the values shown in the following table. Other values are processed as 0.

Value Output
0 (default)
No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

1
Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

2
No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

126
Equivalent to style 2 when converting to char(n) or varchar(n)

to add comma as the thousand separator

CELKO (12/1/2012)

---

NO! Display formatting is done in the presentation layers of any tiered architecture. The proprietary MONEY data type is left-over from the 1970's Sybase dialect. It formats numeric values to look like they had been written with a COBOL PICTURE clause.

01 FOOBAR PICTURE $999,999,999,999V9999

We do not do this in SQL any more.

Lynn Pettis (12/1/2012)

---

Joe, you're a dinosaur like me. Most people on this forum probably don't even know what COBOL is.

Joe, you're a dinosaur like me. Most people on this forum probably don't even know what COBOL is.

CELKO (12/3/2012)

---

And most of them have never see a punch card or used a mag tape drive. But they keep re-inventing them in SQL.

dogramone (12/3/2012)

---

And unfortunately none of them are intelligent enough to investigate. Oh for the good old days when IT was a black box that no one got involved in unless they were truly inspired. Now it's become jut like accounting, a degree and job only and in it jut for the money.

Joe please keep hitting them with a hammer, eventually it might imprint somewhere.

dogramone (12/3/2012)

---

And unfortunately none of them are intelligent enough to investigate. Oh for the good old days when IT was a black box that no one got involved in unless they were truly inspired. Now it's become jut like accounting, a degree and job only and in it jut for the money.

Joe please keep hitting them with a hammer, eventually it might imprint somewhere.