Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Can you please any one explain? Expand / Collapse
Author
Message
Posted Friday, August 9, 2013 1:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 7, 2013 1:56 AM
Points: 15, Visits: 36
Difference between CAST and Convert functions in SQL SERVER??
Post #1482662
Posted Friday, August 9, 2013 1:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:20 PM
Points: 13,252, Visits: 10,133
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1482665
Posted Friday, August 9, 2013 7:11 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
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
Post #1482758
Posted Friday, August 9, 2013 5:07 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:48 PM
Points: 8,545, Visits: 9,034
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
Post #1482977
Posted Friday, August 9, 2013 7:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1482989
Posted Sunday, August 11, 2013 12:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:20 PM
Points: 13,252, Visits: 10,133
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)




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1483103
Posted Tuesday, August 13, 2013 12:54 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:41 PM
Points: 522, Visits: 234
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.

Post #1483908
Posted Tuesday, August 13, 2013 2:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1483981
Posted Tuesday, August 13, 2013 2:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:20 PM
Points: 13,252, Visits: 10,133
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1483982
Posted Tuesday, August 13, 2013 10:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 7, 2013 1:56 AM
Points: 15, Visits: 36
i m using SQL server 2005
Post #1484066
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse