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.