The CAST() system function is one of the most useful tools we have available to us in Microsoft SQL Server. We basically use it to convert a value from one data type to another on-the-fly.
I was using CAST() in a query recently and encountered an interesting problem. If you use CAST(), you need to understand this important rule:
You cannot use a user-defined data type in the CAST() function
Let’s set up a quick example. Here is some code where we use CAST() to cast an integer to a decimal:
Let’s create a user-defined data type to represent DECIMAL(12,4). We’ll call it BigDecimal:
Now let’s try to use this BigDecimal type in our CAST() function:
We get an error message saying: Type BigDecimal is not a defined system type.
Folks, the CAST() function simply does not accept a user-defined data type as the target data type. We need to use the original data type instead.
The same thing is true for CONVERT()
We encounter the same problem if we use the CONVERT() function instead:
Again, we need to use the original, system data type. If you’re not sure what system data type a user-defined data type represents, you can see that in the object explorer under Programmability | Types | User-Defined Data Types:
Next Steps:
Leave a comment if you found this tutorial helpful!
If you want to learn more about common data types in SQL Server, you should definitely download the following FREE guide:
FREE EBook on SQL Server Data Types!
This Ebook contains a description of the most common data types you will encounter during your career as a database professional, condensed into a simple short Ebook. It is important for you to understand these data types and to know how to use them. Download it today!
Make sure you check out the full tutorial on SQL Server user-defined data types:
SQL Server User-Defined Data Types: Explained for Beginners
Here’s the full beginner-friendly tutorial on CAST() and CONVERT() for you to read also:
CAST and CONVERT: A How-To Guide
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
