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 1234»»»

Cast vs. Convert Expand / Collapse
Author
Message
Posted Sunday, March 7, 2010 1:20 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, May 27, 2010 1:31 PM
Points: 132, Visits: 101
Comments posted to this topic are about the item Cast vs. Convert
Post #878278
Posted Monday, March 8, 2010 2:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:21 AM
Points: 2,988, Visits: 901
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.
Post #878473
Posted Monday, March 8, 2010 3:16 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 1:26 AM
Points: 1,657, Visits: 2,083
I liked the question. I got it wrong and I learnt something.
Thank you.
Post #878480
Posted Monday, March 8, 2010 3:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 3, 2014 12:21 AM
Points: 2,122, Visits: 306
Which capabilities is CAST missing when it "has less capabilities in SQL Server"?
Post #878482
Posted Monday, March 8, 2010 3:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 1:28 AM
Points: 2,105, Visits: 5,393
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/
Post #878494
Posted Monday, March 8, 2010 5:10 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 10:00 AM
Points: 8,551, Visits: 9,043
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
Post #878523
Posted Monday, March 8, 2010 5:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:55 PM
Points: 6,582, Visits: 8,860
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #878539
Posted Monday, March 8, 2010 8:16 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 2:32 PM
Points: 454, Visits: 172
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.
Post #878645
Posted Monday, March 8, 2010 8:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:36 AM
Points: 11,192, Visits: 11,091
I am shocked and appalled that Jeff hasn't posted anything about code portability being a myth yet.
Very disappointing

I love CONVERT.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #878646
Posted Monday, March 8, 2010 8:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 9:05 AM
Points: 90, Visits: 132
CONVERT works across all platforms & has more capabilities in SQL Server is also true
Post #878648
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse