Table of contentsImplicit data conversionExplicit Type Conversions with CAST AND CONVERTCASTCONVERTCONVERT DATETIME
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.
When working with NULL, you’ll often find yourself wanting to replace these “missing” values, with something else. Just use ISNULL to do
The SQL ISNUMERIC function checks the input expression for a valid numeric data type. It returns 1 if the input value is
Use the SQL ISNULL function to evaluate the input expression for a NULL value. If it is NULL, then ISNULL replaces it
Use the SQL CONVERT function to change an input expression from one data type into another specified data type. For example, a
The SQL CAST function converts an input expression of one data type into another data type. Description Data types of values often
Knowing when to use the SQL COALESCE function is a lifesaver when you’re dealing with NULL. As you know, NULL is a