Cast vs. Convert

  • agrawal.prakriti

    Mr or Mrs. 500

    Points: 572

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

  • Rune Bivrin

    SSCertifiable

    Points: 7874

    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: 5154

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

    Thank you.

  • ma-516002

    Hall of Fame

    Points: 3359

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

  • Adi Cohn-120898

    SSC-Dedicated

    Points: 33944

    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

    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: 95392

    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: 813

    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: 150442

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

    Very disappointing :laugh: 😉

    I love CONVERT.

  • bouarroudj Mohamed

    Old Hand

    Points: 348

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

  • sknox

    SSChampion

    Points: 12294

    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: 12294

    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: 281252

    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: 281252

    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

    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

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

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