SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cast vs. Convert


Cast vs. Convert

Author
Message
sknox
sknox
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2636 Visits: 2832
I've done a lot of SQLXML programming with simple queries in XML files and XSLT files to transform those query results to HTML pages. I used CONVERT a lot to convert money and datetime values to varchar in the format I needed before it even gets to the XSLT, saving a lot of messy XSL formatting on the front end.

I'm quite surprised that, at the time I answered the question, approximately 3/4 of people got this question wrong. Just under 1/3 of respondents thought there was no real difference! This question, to me, is T-SQL 101. Even if you don't know that CONVERT() is T-SQL-specific, you should know the differences in inputs and potential outputs.

This is not criticism of those who got the question wrong: we all have holes in our knowledge. But it does highlight the value of this QoTD. Before this question, I would have assumed this to be a universally-known topic in MSSQL circles. Now I know I can't make that assumption. And now those who didn't know of the difference have learned something.

Great question!
sknox
sknox
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2636 Visits: 2832
Rune Bivrin (3/8/2010)
Good question, but which alternative is correct depends on how you define platform. If you define "platform" as "versions of SQL Server", then CONVERT works in every version whereas CAST was introduced in 7.0 (IIRC - could've been 2000).


Well, if you define "black" as "white", you can prove anything. The standard definition of platform is independent of versions of specific platforms. I think it's quite ironic that you're criticizing a question that compares an ANSI standard function to a vendor-specific function by relying on a non-standard definition.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32884 Visits: 18560
Good question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32884 Visits: 18560
Paul White (3/8/2010)
I am shocked and appalled that Jeff hasn't posted anything about code portability being a myth yet.
Very disappointing Laugh ;-)

I love CONVERT.



He'll be in later to handle that.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14461 Visits: 12229
Paul White (3/8/2010)
I am shocked and appalled that Jeff hasn't posted anything about code portability being a myth yet.
Very disappointing Laugh ;-)

I love CONVERT.

I'm sure he will soon.
Worrying about portability is crazy until we get a relational dbms with a relational language - then we can just worry about porting to that.Whistling

Tom

Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1263 Visits: 1499
I thought I knew the answer, but BZZZZZZT!

Nice question.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

erwin oosterhoorn
erwin oosterhoorn
SSC Eights!
SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)

Group: General Forum Members
Points: 918 Visits: 387
bouarroudj Mohamed (3/8/2010)
CONVERT works across all platforms & has more capabilities in SQL Server is also true


That is what I am looking at as well, does this not mean the same as the given right answer?
If one has less the other must have more?
KevinC.
KevinC.
Right there with Babe
Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)

Group: General Forum Members
Points: 785 Visits: 504
erwin.oosterhoorn (3/8/2010)
bouarroudj Mohamed (3/8/2010)
CONVERT works across all platforms & has more capabilities in SQL Server is also true


That is what I am looking at as well, does this not mean the same as the given right answer?
If one has less the other must have more?


If I understand correctly, CONVERT is MS SQL specific whereas CAST is ANSI-SQL and will work on multiple platforms (i.e. Oracle, DB2, etc.).

Therefore, the above statement is incorrect because CONVERT does NOT work across all platforms (though it does have more capabilities in SQL Server).

Please correct me if I'm wrong. :-D

--
Kevin C.
erwin oosterhoorn
erwin oosterhoorn
SSC Eights!
SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)

Group: General Forum Members
Points: 918 Visits: 387
KevinC. (3/8/2010)
erwin.oosterhoorn (3/8/2010)
bouarroudj Mohamed (3/8/2010)
CONVERT works across all platforms & has more capabilities in SQL Server is also true


That is what I am looking at as well, does this not mean the same as the given right answer?
If one has less the other must have more?


If I understand correctly, CONVERT is MS SQL specific whereas CAST is ANSI-SQL and will work on multiple platforms (i.e. Oracle, DB2, etc.).

Therefore, the above statement is incorrect because CONVERT does NOT work across all platforms (though it does have more capabilities in SQL Server).

Please correct me if I'm wrong. :-D

--
Kevin C.


Thanks Kevin, if you are correct than that makes sense now.
Rune Bivrin
Rune Bivrin
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3910 Visits: 1502
sknox (3/8/2010)
Rune Bivrin (3/8/2010)
Good question, but which alternative is correct depends on how you define platform. If you define "platform" as "versions of SQL Server", then CONVERT works in every version whereas CAST was introduced in 7.0 (IIRC - could've been 2000).


Well, if you define "black" as "white", you can prove anything. The standard definition of platform is independent of versions of specific platforms. I think it's quite ironic that you're criticizing a question that compares an ANSI standard function to a vendor-specific function by relying on a non-standard definition.

Who says I'm criticizing the question?

I'm merely pointing out that since this is SQL Server Central, and not DBMS central, such an interpretation isn't completely out of order. And from that point of view CONVERT is somewhat more universal, even if I sincerely hope noone still wrestles with SQL Server 6.5...


Just because you're right doesn't mean everybody else is wrong.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search