erwin.oosterhoorn (3/8/2010)
bouarroudj Mohamed (3/8/2010)
CONVERT works across all platforms & has more capabilities in SQL Server is also trueThat is what I am looking at as well, does this not mean the same as the given right answer?
If one has less the other must have more?
I agree with this. The phrasing of the answers seems quite ambiguous to me.
"CAST ... has less capabilities in SQL Server" – according to what? According to "CAST in another RDBMS", according to "CAST in SQL-92", or according to "CONVERT in MSSQL/another RDBMS"?
KevinC. (3/8/2010)
If I understand correctly, CONVERT is MS SQL specific whereas CAST is ANSI-SQL and will work on multiple platforms (i.e. Oracle, DB2, etc.).Therefore, the above statement is incorrect because CONVERT does NOT work across all platforms (though it does have more capabilities in SQL Server).
Please correct me if I'm wrong. 😀
CONVERT is not MSSQL specific, it also works on Sybase ASA/ASE.
Also CONVERT exists in SQL-92, but has different meaning (see below).
I had checked documentation on several RDBMS platforms (MSSQL, Sybase, Oracle, DB2, PostgreSQL, MySQL) before I answered the question, here is the result:
– CAST works across all these platforms, and may have additional MULTISET parameter in Oracle;
– CONVERT works across all platforms, but has two different meanings. In MSSQL/Sybase it converts data from one type to another, in other databases it converts text data from one encoding to another (therefore working like COLLATE clause in MSSQL), as described in SQL-92.
Also I found an issue in the QOD explanation:
agrawal.prakriti
CAST is the more ANSI-standard of the two functions, meaning that while it's more portable
This sentence is somewhat incomplete, isn't it? (although I'm a total noob at English language 🙂 )