SQL Conversion Functions
Use SQL conversion functions to work with different data types. SQL server uses data types to store a specific kind of value such as numbers, dates, or text in table columns and to use in functions, such as mathematical expressions. One issue with data types is that they don’t usually mix well. Conversion functions make them mix better!
Though there are times when the DBMS automatically (implicitly) converts types from one type to another, in other cases, SQL needs more convincing. In these cases, the CAST and CONVERT functions come into play.
If you not familiar with SQL functions, then I would recommend staring with the Introduction to SQL Server Built-In Functions.
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can use my guide Getting Started Using SQL Server to jump start your progress.
SQL Conversion Functions – Convert from One Data Type to Another Using Cast and Convert
As we write expression used within the database, it is common for our data to go through several transformations. For instance, If you write a DATADIFF formula, and the input date is in VARCHAR format, the DBMS implicitly converts the value to the date data type.
More commonly it is our responsibility to convert data from one type to another using CAST and CONVERT.
Latest Posts
-
SQL Data Conversions
Implicit data conversion Implicit conversions are those conversions that occur automatically whenever the CAST or CONVERT functions aren’t used. Not all values can be implicitly converted to another data type. The following chart shows what can be implicitly converted for the common data types we’ve previously covered: Please keep in mind this chart shows what…
-
Replace Missing Values in your Queries using ISNULL
When working with NULL, you’ll often find yourself wanting to replace these “missing” values, with something else. Just use ISNULL to do so. In this video I’ll walk through some of the example and you’ll see it is pretty easy to use. If you like what you are seeing, then why not get some super…
-
SQL ISNUMERIC Function (Transact SQL)
The SQL ISNUMERIC function checks the input expression for a valid numeric data type. It returns 1 if the input value is numeric and 0 if it is not. Description The ISNUMERIC function in SQL tells if the input expression can be converted to a numeric value or not. It returns an integer as output…
-
SQL ISNULL Function (Transact SQL)
Use the SQL ISNULL function to evaluate the input expression for a NULL value. If it is NULL, then ISNULL replaces it with another specified value. Description The ISNULL function helps to deal with the NULL values in database records. If we want to view a column with NULL values, we can handle them using…
-
SQL CONVERT Function (Transact SQL)
Use the SQL CONVERT function to change an input expression from one data type into another specified data type. For example, a decimal value to an integer or a date value to a string. Description The SQL CONVERT function is one of the SQL functions to change the data type of a variable or an…
-
SQL CAST Function (Transact SQL)
The SQL CAST function converts an input expression of one data type into another data type. Description Data types of values often convert to meet different format requirements in SQL queries. Sometimes these are implicit conversions. For example, while multiplying a decimal value with an integer value, the SQL engine first converts the integer value…
-
SQL COALESCE Function and NULL
Knowing when to use the SQL COALESCE function is a lifesaver when you’re dealing with NULL. As you know, NULL is a tricky concept, and it seem what ever NULL “touches” in an expression, it renders the result NULL. So, when you’re dealing with NULL, how can you break out of the cycle? That is,…