Can you please any one explain?

Question

Can you please any one explain?

techmarimuthu

SSC Enthusiast

Points: 137
More actions
August 9, 2013 at 1:02 am

#281684

Difference between CAST and Convert functions in SQL SERVER??

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

TomThomson SSC Guru Points: 104773 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

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)

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

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

Snigdha Vartak Default port Points: 1486 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

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)

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 8

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

techmarimuthu SSC Enthusiast Points: 137 More actions · Answer 9

techmarimuthu

SSC Enthusiast

Points: 137

August 13, 2013 at 10:30 pm

#1641210

i m using SQL server 2005

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 10

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