December 1, 2012 at 8:33 pm
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
December 1, 2012 at 9:01 pm
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)
December 1, 2012 at 9:16 pm
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.
December 3, 2012 at 2:29 pm
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 performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
December 3, 2012 at 6:06 pm
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. :laugh:
And that's part of the problem, they have no idea what you are even talking about.
December 3, 2012 at 8:38 pm
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.
December 3, 2012 at 8:45 pm
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. 🙂
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 3, 2012 at 8:58 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply