Using CAST() function with User-Defined Data Types…Did you know…

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.

One thought on “Using CAST() function with User-Defined Data Types…Did you know…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.