CAST and CONVERT

[Rhys666]

What is the technical difference berween CAST and CONVERT in MS SQL Server 2000? I mean performance, memory utilisation, best practice and preferred practice wise, the whole big fat Enchilada, (mmm, hungry now!)


Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[Ankor]

Hey! Here is what I fond in Books Online: "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." This means that you can have something like select convert(varchar(25), getdate(), 11), but there is no way to use style in CAST.

Well, I tried to run two really huge queries to check what is faster CAST or CONVERT. They are EXACTLY the same. The query cost is equal to the very last decimal point. So I guess the main point is whether you need your code to be compatible with SQL-92 or not.

 

[Rhys666]

I was just curious, and do nit have access to my PC with all the documentation on-board at the moment! Can't remember all detail, but is it right that you can't use CONVERT to go from some data types to others, and if it is does this also apply to CAST?
Again, more curiosity than anything as theres been a few 'HOW DO I CONVERT...' posts recently, and I don't have any documentation with me...

Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!