- Posted by Cláudio Silva
- On June 5, 2018
- 0 Comments
- CAST, CONVERT, Firebird, SQLServer, UDDT, USER-DEFINED DATA TYPES
Using CAST() function with User-Defined Data Types…Did you know… was originally posted in Cláudio Silva’s Blog
I’m converting some Firebird database code to T-SQL and I’m seeing a lot of code that doesn’t work in the same way that SQL Server.
No surprise – I already expected that but for those that still say that “all engines/databases are equal”…”is SQL right?” here is another proof that is not true.
On Firebird it is possible to use the CAST function using a DOMAIN (the equivalent in SQLServer is UDDT – User-Defined Data Types) as target data-type, well turns out that on SQL Server…that is not possible.
Which means that, even if I have created a UDDT as:
CREATE TYPE dbo.VARCHAR_50 FROM VARCHAR(50);
The following code will not work
SELECT CAST('value' AS VARCHAR_50)
and it yields the following error message:
Msg 243, Level 16, State 2, Line 1
Type VARCHAR_50 is not a defined system type.
this means that we need to change it to the defined system type like:
SELECT CAST('value' as VARCHAR(50))
Maybe it works with CONVERT() function?!…not really, the behaviour is the same.
To finish the title…Using CAST() function with User-Defined Data Types…Did you know… it is not possible. You need to use the system type.
Thanks for reading.