## Thousand Seperator Magic

 Author Message karthik M SSCoach Group: General Forum Members Points: 15817 Visits: 2588 All,I have recently faced the below scenario.i.e to add comma as the thousand seperatorI 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' ? karthik Lynn Pettis SSC Guru Group: General Forum Members Points: 226148 Visits: 40424 karthik M (12/1/2012)All,I have recently faced the below scenario.i.e to add comma as the thousand seperatorI 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' ?From Books Online, CONVERT function:money and smallmoney StylesWhen 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) Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) Lynn Pettis SSC Guru Group: General Forum Members Points: 226148 Visits: 40424 CELKO (12/1/2012) to add comma as the thousand separator 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,999V9999We do not do this in SQL any more. Joe, you're a dinosaur like me. Most people on this forum probably don't even know what COBOL is. Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) CapnHector SSCrazy Eights Group: General Forum Members Points: 8265 Visits: 1789 Lynn Pettis (12/1/2012)CELKO (12/1/2012) to add comma as the thousand separator 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,999V9999We do not do this in SQL any more. Joe, you're a dinosaur like me. Most people on this forum probably don't even know what COBOL is.Isn't that a shade of blue :-D as far as why to do it, there are some limited times when i want my results window to look preaty on a couple of queries i run when checking our platform. I would prefer to leave it to the UI but when the UI does not have the options (SSMS) in the SQL is the only place to really do it. For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail ShawNeed to Split some strings? Jeff Moden's DelimitedSplit8KJeff Moden's Cross tab and Pivots Part 1Jeff Moden's Cross tab and Pivots Part 2 Lynn Pettis SSC Guru Group: General Forum Members Points: 226148 Visits: 40424 CELKO (12/3/2012)Joe, you're a dinosaur like me. Most people on this forum probably don't even know what COBOL is. And most of them have never see a punch card or used a mag tape drive. But they keep re-inventing them in SQL. And that's part of the problem, they have no idea what you are even talking about. Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) dogramone SSCommitted Group: General Forum Members Points: 1742 Visits: 4013 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. Evil Kraig F SSC Guru Group: General Forum Members Points: 53043 Visits: 7660 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.Oh, don't worry, he definately makes an impression. You bring up a good point though. Eventually (we're getting there) the tools become good enough that the average person can roll their own for small projects. They may not be a professional cabinet maker but the average person just needs to be able to slap their IKEA bookshelf together. Quality will always be required at some level, but every contacts database out there really doesn't (usually) need top-end computer science knowledge. However, I'm helping to horribly derail this poor thread, and probably shouldn't soapbox that topic here. Though, I do have a new blog post idea. :-) - Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake. For better assistance in answering your questions | Forum NetiquetteFor index/tuning help, follow these directions. |Tally TablesTwitter: @AnyWayDBA Lynn Pettis SSC Guru Group: General Forum Members Points: 226148 Visits: 40424 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.I'm sorry, but Mr. Celko insults the intelligence of others enough, we don't need someone else following in his unprofessional foot steps. Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)