Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Thousand Seperator Magic Expand / Collapse
Author
Message
Posted Saturday, December 1, 2012 8:33 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
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' ?


karthik
Post #1391688
Posted Saturday, December 1, 2012 9:01 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:54 PM
Points: 23,227, Visits: 31,921
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' ?



From Books Online, CONVERT function:



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)





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1391690
Posted Saturday, December 1, 2012 9:01 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:49 PM
Points: 1,945, Visits: 2,894
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,999V9999

We do not do this in SQL any more.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1391691
Posted Saturday, December 1, 2012 9:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:54 PM
Points: 23,227, Visits: 31,921
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,999V9999

We 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 Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1391695
Posted Monday, December 3, 2012 2:29 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:40 PM
Points: 945, Visits: 1,771
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,999V9999

We 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 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 Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1392149
Posted Monday, December 3, 2012 3:56 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:49 PM
Points: 1,945, Visits: 2,894
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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1392174
Posted Monday, December 3, 2012 6:06 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:54 PM
Points: 23,227, Visits: 31,921
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 Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1392205
Posted Monday, December 3, 2012 8:38 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:21 PM
Points: 593, Visits: 2,877
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.



Post #1392232
Posted Monday, December 3, 2012 8:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 6,236, Visits: 7,379
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 Farrell

Never 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 Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1392233
Posted Monday, December 3, 2012 8:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:54 PM
Points: 23,227, Visits: 31,921
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 Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1392235
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse