Can you please any one explain?

  • Difference between CAST and Convert functions in SQL SERVER??

  • Essentially they do the same, but you CONVERT has an optional third parameter you can use for formatting (mostly for conversions between dates and strings).

    CAST is defined in the ANSI (or ISO or however you call it) SQL defination and is thus standard over all database platforms (or it should be), while CONVERT is more SQL Server specific.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I've seen many debates on CAST versus CONVERT and most center around performance. One comparison goes one way and the next one goes the other way. In the end, it depends on the specific test, but I've found them to pretty-much be a wash. Personally, I use CONVERT, but that just personal preference.

  • Ed Wagner (8/9/2013)


    I've seen many debates on CAST versus CONVERT and most center around performance. One comparison goes one way and the next one goes the other way. In the end, it depends on the specific test, but I've found them to pretty-much be a wash. Personally, I use CONVERT, but that just personal preference.

    Personally, I'm lazy: in the cases where CAST is good enough, I use CAST because it means less typing. In the case where it isn't, I use CONVERT.

    Tom

  • techmarimuthu (8/9/2013)


    Difference between CAST and Convert functions in SQL SERVER??

    My recommendation would be to get into SSMS and press the {f1} key. There's a whole lot of good information on this very subject in Books Online.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (8/9/2013)


    techmarimuthu (8/9/2013)


    Difference between CAST and Convert functions in SQL SERVER??

    My recommendation would be to get into SSMS and press the {f1} key. There's a whole lot of good information on this very subject in Books Online.

    The page you get when you press F1 on cast or convert isn't exactly helpful, it's a stub referring you to another page 😀

    (but I get the point, MSDN is a good resource for basic questions like this)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • CONVERT is specific to SQL Server, and allows for a greater breadth of flexibility when converting between date and time values, fractional numbers, and monetary signifiers.

    CAST is the more ANSI-standard of the two functions, meaning that while it's more portable (i.e., a function that uses CAST can be used in other database applications more or less as-is), it's also less powerful. CAST is also required when converting between decimal and numeric values to preserve the number of decimal places in the original expression.

    For those reasons, it's best to use CAST first, unless there is some specific thing that only CONVERT can provide in the work you're doing.

  • Koen Verbeeck (8/11/2013)


    Jeff Moden (8/9/2013)


    techmarimuthu (8/9/2013)


    Difference between CAST and Convert functions in SQL SERVER??

    My recommendation would be to get into SSMS and press the {f1} key. There's a whole lot of good information on this very subject in Books Online.

    The page you get when you press F1 on cast or convert isn't exactly helpful, it's a stub referring you to another page 😀

    (but I get the point, MSDN is a good resource for basic questions like this)

    What version are you using? I ask because I get a whole lot more than just a stub from BOL.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (8/13/2013)


    Koen Verbeeck (8/11/2013)


    Jeff Moden (8/9/2013)


    techmarimuthu (8/9/2013)


    Difference between CAST and Convert functions in SQL SERVER??

    My recommendation would be to get into SSMS and press the {f1} key. There's a whole lot of good information on this very subject in Books Online.

    The page you get when you press F1 on cast or convert isn't exactly helpful, it's a stub referring you to another page 😀

    (but I get the point, MSDN is a good resource for basic questions like this)

    What version are you using? I ask because I get a whole lot more than just a stub from BOL.

    Ah, it seems to be solved in SQL Server 2012.

    I'm still using SQL Server 2008R2 at the client.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • i m using SQL server 2005

  • techmarimuthu (8/13/2013)


    i m using SQL server 2005

    Don't worry, you can use CONVERT and CAST in SQL Server 2005.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 11 posts - 1 through 10 (of 10 total)

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