Cast vs. Convert

Question

Cast vs. Convert

agrawal.prakriti

Mr or Mrs. 500

Points: 572
More actions
March 7, 2010 at 1:20 am

#217500

Comments posted to this topic are about the item Cast vs. Convert

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Rune Bivrin SSCrazy Eights Points: 8511 More actions · Answer 1

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).

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

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 2

I liked the question. I got it wrong and I learnt something.

Thank you.

ma-516002 Hall of Fame Points: 3359 More actions · Answer 3

Which capabilities is CAST missing when it "has less capabilities in SQL Server"?

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

Convert has a third parameter that is optional and is called style. It is mostly used with dates. For example check out the next script:

declare @dt char(10)

select @dt = '31/01/2010'

--This will always work

select convert(smalldatetime, @dt, 103)

--This might work and might fail

select cast (@dt as smalldatetime)

go

declare @dt char(10)

select @dt = '01/31/2010'

--This will always work

select convert(smalldatetime, @dt, 101)

--This might work and might fail

select cast (@dt as smalldatetime)

Notice that with the convert function, I was able to work with the string as date regardless of the format that I was using because of the third parameter, but when I used cast function only one format could be cast to smalldatetime.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

TomThomson SSC Guru Points: 104773 More actions · Answer 5

Good question, correct answer (well, it does depend on how one interprets "platform" but it's correct for what I think is the natural interpretation) but the explanation is a little lacking in that it appears to suggest that the style parameter of convert is only for datetime, whereas in fact it also is used for binary, float, real, money, smallmoney, and xml types.

Tom

WayneS SSC Guru Points: 95460 More actions · Answer 6

CONVERT has the third parameter "style" which can be used when converting datetime to string

The style parameter can also used when converting float/real, money/smallmoney, xml and binary/varbinary/char/varchar.

See BOL for more information.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Brnbngls SSC Eights! Points: 823 More actions · Answer 7

I took platforms to mean various RDMS' (Oracle, Sybase, etc.). And I knew CAST worked in Oracle (since that's where I'm spending alot of time lately) but wasn't sure about CONVERT. Took a gamble, got it right.

The distance between genius and insanity is measured only by success.

Paul White SSC Guru Points: 150467 More actions · Answer 8

I am shocked and appalled that Jeff hasn't posted anything about code portability being a myth yet.

Very disappointing :laugh: 😉

I love CONVERT.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

bouarroudj Mohamed Old Hand Points: 378 More actions · Answer 9

CONVERT works across all platforms & has more capabilities in SQL Server is also true

sknox SSChampion Points: 12388 More actions · Answer 10

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 SSChampion Points: 12388 More actions · Answer 11

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 SSC Guru Points: 281334 More actions · Answer 12

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[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

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[/url]
Learn Extended Events

TomThomson SSC Guru Points: 104773 More actions · Answer 14

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