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

what is the difference between CAST and CONVERT Function? Expand / Collapse
Author
Message
Posted Monday, April 21, 2008 12:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 11, 2012 5:22 AM
Points: 201, Visits: 51
what is the difference between CAST and CONVERT Function?
which one is preferred?
Post #487723
Posted Monday, April 21, 2008 1:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 10:50 PM
Points: 120, Visits: 211
hi

refere this url

http://searchwinit.techtarget.com/tip/0,289483,sid1_gci1014050,00.html
Post #487738
Posted Monday, April 21, 2008 9:38 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: Yesterday @ 7:21 AM
Points: 3,836, Visits: 1,085
Use the conversion functions, CAST and CONVERT, to convert expressions of one data type to another data type when these conversions are not performed automatically by Microsoft® SQL Server™ 2000. These conversion functions are also used to obtain a variety of special data formats. Either of the conversion functions can be used in the select list, in the WHERE clause, and anywhere an expression is allowed.

Use CAST rather than CONVERT if you want Transact-SQL program code to comply with SQL-92. Use CONVERT rather than CAST to take advantage of the style functionality in CONVERT.




Post #488058
Posted Tuesday, April 22, 2008 3:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:18 AM
Points: 2,886, Visits: 3,256
Apart from the datetime formatting capabilities of CONVERT, CAST and CONVERT do identical jobs.

Personally I prefer to use CAST in all situations except where I want to format datetime to a specified character string. I then treat CONVERT as a datetime-specific routine. This is just because I prefer to use ANSI standard code where I have a choice - other people may prefer to use CONVERT in all cases.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #488442
Posted Tuesday, April 22, 2008 7:04 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353
EdVassie (4/22/2008)
Apart from the datetime formatting capabilities of CONVERT, CAST and CONVERT do identical jobs.

Not quite true.

Conversions of float, real, money, or smallmoney to character data types also use styles.

Check BOL fo details.
Post #489005
Posted Wednesday, April 23, 2008 12:07 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
vyas (4/21/2008)
Use the conversion functions, CAST and CONVERT, to convert expressions of one data type to another data type when these conversions are not performed automatically by Microsoft® SQL Server™ 2000. These conversion functions are also used to obtain a variety of special data formats. Either of the conversion functions can be used in the select list, in the WHERE clause, and anywhere an expression is allowed.

Use CAST rather than CONVERT if you want Transact-SQL program code to comply with SQL-92. Use CONVERT rather than CAST to take advantage of the style functionality in CONVERT.


ANSI 92 is only good if software vendors follow it. Try CAST from a VARCHAR to a DATE/TIME in Oracle and see what happens....

People spend way too much time following a standard that isn't followed by the people writing the RDBMS's. Portability is a myth.


--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 #489061
Posted Friday, June 18, 2010 4:20 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, October 9, 2014 7:27 AM
Points: 648, Visits: 254
Just been testing some data and found this difference between cast and convert.
When you cast a number to real of size -1.18E-39 you expect an conversion error, so removing a zero should be OK. You have to remove zero's until -1.18E-36 before the error is not thrown.
Using convert you can use up to -1.18E-38 before the number is changed into a 0 (zero)
select cast(-0.00000000000000000000000000000000000000118 as real) as [cast result]
select convert(real, '-0.00000000000000000000000000000000000000118') as [convert result]

(take away zero's from cast until you get a result)

Does anyone knows why this is?
Post #939461
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse