All Things SQL

All Things Related To SQL, Databases & Cloud Based Solutions

SQL Functions 

SQL CAST & CONVERT Function – Modifying Data Types

, , , , , , ,

There are a few ways (depending on the database) where we can convert a column data type. The main two functions used in SQL to modify a column data type are the CAST function and the CONVERT function.

The difference between CAST vs CONVERT is minimal, but we commonly see the sql CAST() function being used more often. This is because it’s considered an ANSI-SQL standard function. As a result, this mean all databases using SQL should support it. Looking at a general example, we could use cast to change an int data type to a varchar data type in SQL.

While CAST can be used in almost any database, CONVERT is specific to SQL Server databases. Using the CONVERT function becomes very useful when altering date and time formats. We can use the official Microsoft documentation to refer to the conversion codes in section J. This adds for additional flexibility that using CAST cannot.

Some reasons why we may want to change a column(s) data type would be to cast:

  • A string to an integer so the field can be used for aggregations
  • An integer to a decimal to avoid rounding errors
  • A string field to text due to string size constraints
Syntax Examples for SQL CAST & CONVERT Function

We can look at the following syntax example using CAST() to show us the format for most SQL relational databases.

SELECT CAST(<column_name> AS datatype) AS <new_column_name> FROM <table_name>;

Similarly, we can also use CONVERT() in the same way to accomplish the same task.

SELECT CONVERT(datatype, <column_name>) AS <new_column_name> FROM <table_name>;

Practical Examples of Casting Column Data Types

We are almost always casting values because the current data format doesn’t work for our specific needs. As mentioned before, we could be cutting off data from tables if we do not specify the proper data type in our table.

Another issue that comes up often are rounding errors. This happens a lot of the time when the INT data type is declared and we only have whole numbers when we need decimals. We can look at a few examples of how we use the CAST and CONVERT functions.

sql cast int to decimal, sql convert int to decimal
Example of casting int to decimal in SQL
sql cast int to varchar, sql convert int to varchar
Example of casting int to varchar in SQL

We can view the above example to see a new column being created that combines the employee id and employee last name using concatenation. Normally, we would not be able to combine these fields since the data types would be incompatible since EmployeeId is an integer column. As a result, we can cast int to varchar using SQL before combining fields. Some databases will support the combination without having to cast the data type and some won’t. We can also look at the SQL Server equivalent using CONVERT instead.

sql server convert int to varchar, convert int to varchar sql server
Example of using convert from int to varchar in SQL

In another example, we can demonstrate the conversion of today’s datetime value in SQL Server to a new format.

sql server convert date format, convert date format sql server
Converting current date in SQL Server to ‘YYYYMMDD’ format
Final Thoughts

Casting values can come in handy as we’ve seen several practical examples. Whether we’re trying to maintain pinpoint accuracy in our calculations or ensuring we’re not trimming data, the CAST() function proves most useful. CONVERT() takes things a step further and allows users of SQL Server an upper-hand shortcut for converting date and timestamp fields.