SQL CAST Function for Data Type Conversions


By:   |   Updated: 2021-05-25   |   Comments (2)   |   Related: More > T-SQL


Problem

In a perfect world, each column in a SQL Server database table has the optimal data type (date, varchar, decimal, integer, bigint, etc.) for the data stored in it. In the real world, data requirements change constantly. The data type you chose in the tables original build no longer fits the needs of the new table requirements.

For example, you may have originally stored a date column in your table as character data (string) and now you need to store the data as a "date" data type.

Solution

In this tutorial, we will examine how to use the CAST operator to update your table columns to accommodate changes brought on by new technology and the ever-changing requirements for data storage in SQL scripts or stored procedures.

What is the SQL CAST Function?

The short answer: The SQL CAST function is used to explicitly convert a given data type to a different data type in a SQL database. Although, there are some restrictions.

The long answer: CAST is an ANSI SQL standard that came about with SQL-92, meaning that it is portable across different Database Management Systems (DBMS) and will work the same on Microsoft SQL Server, MySQL, Oracle and so on.

You cannot just "convert" (by convert, I mean SQL Server CAST) any data type to one of your choice at will. The two data types must be compatible or similar in nature. For example, you cannot CAST a string data type to a varbinary data type.

There are some exceptions to this rule; for example you can use CAST to convert a CHAR(10) date in a character string to a "date" data type, but you cannot use CAST to convert a CHAR(10) character string like "John Smith" to a "date" data type. You also cannot convert an INT (integer) data type to a SMALLINT (Small Integer) data type if the values stored in the INT column are larger than what is allowed by the SMALLINT data type. Note the specificity in that last statement, it was intentional. You can convert a column from an INT to a SMALL INT as long as the largest value stored in the source table INT column is less than the maximum limit of the SMALL INT which is -32,768 to +32,767. More on that later.

Simple SQL CAST Samples

Below are some simple Transact-SQL code blocks where converting from a string value to a date data type is allowed.

Line 1 is converting a date data type to a string data type and line 2 converts a string data type to a date data type.

SELECT CAST(GETDATE() AS CHAR(12)) AS DateStmp1;
SELECT CAST('08/24/2020' AS DATE) AS DateStmp2; 

Results:

query results

The following conversion is not allowed:

SELECT CAST('JohnDoe' AS DATE) AS StringToDate; 

Results:

Msg 245, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.

See the chart below for more information on what data types can and cannot be converted or CAST.

Data Type Conversion Reference

Implicit conversions do not require specification of the CAST or COVERT. However, explicit conversions do require specification. The following chart shows what data types can be converted and which ones cannot.

Data type conversion table

Table courtesy of Microsoft. You can download a copy as a png file at: SQL Server Data Type Conversion Chart

Where can you use SQL CAST

There is a copious number of other instances where the cast function can be used efficiently. As shown in the samples below, you can use the cast function in conjunction with most query commands. However, since it is commonly used in a SELECT statement, the cast function is often forgotten about in other parts of a query that can be far more productive than just the basic SELECT statement when retrieving SQL data.

In the following samples, we will be using the AdventureWorks 2012 database. You can download a copy here.

Use CAST for SELECT

Starting with the most common statement, SELECT, this sample shows how to return a rounded up/down results of a MONEY data type to an INT data type. Unlike the simple SELECT statement shown earlier, this sample will run against a table.

I added the optional "WHERE" clause in order to filter down the results returned. It has no other bearing on the CAST function in the SELECT statement.

SELECT 
   productID 
   , StandardCost 
   , CAST(StandardCost AS INT) AS 'Rounded Price'
FROM Production.ProductCostHistory
WHERE ProductID LIKE '70%';
GO

Results:

query results

Use CAST for INSERT

For the sake of not messing up any tables in the AdventureWorks2012 database, in the next three examples we will create a temporary table for use.

CREATE TABLE castTest(
   colid INT IDENTITY NOT NULL
   , firstName CHAR(20)
   , lastName CHAR(20)
   );
GO

With the INSERT statement, add a row that contains a name for the "firstName" column but put todays date/time value as the data for the last name.

INSERT INTO castTest(firstName, lastName)
VALUES('Smith', CAST('2020-02-02' AS DATETIME));
GO

Run a SELECT query to return the results.

SELECT *
FROM castTest;
GO

Results:

query results

Use CAST for UPDATE

Following suit with the INSERT statement, in this sample, we will be updating the "firstName" column replacing the name Smith with another date/time as shown in the following example.

UPDATE castTest
SET firstName = CAST('2021-03-03' AS DATETIME)
WHERE colid = 1;
GO

Run a SELECT query to return the results.

SELECT *
FROM castTest;
GO

Results:

query results

Use CAST in WHERE

In this sample, we are converting a MONEY data type to an INT data type to provide a rounding function. This will return all values that are between 13.5 and 14.4 as a result.

SELECT 
   productID 
   , StandardCost 
FROM Production.ProductCostHistory
WHERE CAST(StandardCost AS INT) = 14;
GO

Results:

query results

Alternative Code Options

You could also use other functions such as CONVERT and PARSE to perform a similar task to that of the CAST function. While they all seem to do about the same thing, the differences will have an impact on system performance. When in doubt as to which to use, it's always best practice to go with the industry standard. In this case, that would be the CAST function.

CONVERT

The convert function is specific to Microsoft's T-SQL and will not function properly on other DBMSs, unlike the CAST function that is an ANSI standard and is cross platform compatible. The syntax is as follows:

CONVERT (Data_Type, (length), expression/value, style)

Breakdown of the syntax listed above:

  • Data_Type is the target data type to which you want to convert the expression/value.
  • Length is an integer value that specifies the length of the target type. For example; VARCHAR(50)
  • Expression/Value is what you want converted into the desired data type.
  • Style: an optional integer value to select/determine the style format of the output. See Date and Time Conversions Using SQL Server for formatting options.

PARSE

This function, like the CAST and CONVERT, return an expression translated to the requested data type. Only use PARSE for converting from string to date/time and number types. If you need general type conversions, you will need to use the CAST or CONVERT functions.

The syntax for PARSE:

PARSE(expression/value AS data_type [USING culture])
  • Expression/Value is what you want converted into the desired data type.
  • Data_Type is the target data type to which you want to convert the expression/value
  • Culture: an optional string that identifies culture that the "Data_Type" is formatted.

Performance Comparison

Some may argue that CAST and CONVERT are virtually the same and your database won't take a greater hit when using one over the other. PARSE on the other hand, is significantly slower than two former options. Back to reality, the CAST function is faster (in most cases) than its Microsoft counterpart CONVERT. The sample below shows the real time difference between CAST, CONVERT and PARSE in the respect of elapsed time to complete the conversions on a table with only 10 thousand entries. This is minute compared to the real world where you may have tens of millions of data rows to contend with.

Preparing Sample Data

First, let's create a test table to work with. You can do this on one of your test databases or create a new test database that you can dump later.

CREATE TABLE Customers_Temp (
   CustomerID INT NOT NULL
   , CustomerName CHAR(100) NOT NULL
   , CustomerAddress CHAR(100) NOT NULL
   , Comments CHAR(189) NOT NULL
   , LastOrderDate DATE
   , MyDate VARCHAR(50)
   );
GO

For this test, we are going to create 100,000 rows of random data with random dates selected from the last 30 years.

On a side note; the following code is a free copy that you can use elsewhere when you need to generate thousands or millions of rows of data for test purposes. Modify as you see fit.

DECLARE @rdate DATE
DECLARE @adate DATE
DECLARE @startLoopID INT = 1
DECLARE @endLoopID INT = 100000 -- Amount of Rows you want to add
DECLARE @i INT = 1
WHILE (@i <= 100000) -- Make sure this is the same as the "@endLoopId" from above
WHILE @startLoopID <= @endLoopID
BEGIN
    SET @rdate = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 10950 ), '1990-01-01'); -- The "10950" represents 30 years, the date provided is the starting date.
    SET @adate = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 10950 ), '1990-01-01');
    SET @startLoopID = @startLoopID + 1;
 
    INSERT INTO Customers_Temp(CustomerID, CustomerName, CustomerAddress, Comments, LastOrderDate, MyDate)
    VALUES
    (
      @i,
      'CustomerName' + CAST(@i AS CHAR),
      'CustomerAddress' + CAST(@i AS CHAR),
      'Comments' + CAST(@i AS CHAR),
      (@rdate),
      (@adate)
    )
   SET @i += 1;
END

Finally, let's test the speed in which these three functions perform.

SET STATISTICS TIME ON;
SELECT CAST(MyDate AS DATE) FROM Customers_Temp;
SELECT CONVERT(DATE,LastOrderDate) FROM Customers_Temp;
SELECT PARSE(MyDate AS DATE) FROM Customers_Temp;
SET STATISTICS TIME OFF;
GO

Results:

CAST

query statistics time

CONVERT

query statistics time

PARSE

query statistics time

Note: Your results may vary depending on processor speed, etc. but this will give you a general idea.

Summation

As a rule, SQL will automatically convert certain data types implicitly. When you need to force a conversion, (explicitly) you can opt for the CAST, CONVERT or PARSE functions. Looking through the examples above, performance wise, it's better to go with the CAST function. Since CAST is also an ANSI standard, and is cross platform, it should always be your first option.

Next Steps


Last Updated: 2021-05-25


get scripts

next tip button



About the author
MSSQLTips author Aubrey Love Aubrey Love has been a Database Administrator for about 8 years and is currently working as a Microsoft SQL Server Business Intelligence specialist.

View all my tips



Comments For This Article




Wednesday, June 2, 2021 - 12:07:59 PM - Aubrey W Love Back To Top (88789)
Jeff,

Thanks for the comment. Yes, sometimes you just need 100k or a few million rows of generic data for testing. Especially if you performance monitoring.

If anyone else has a generic data entry script thatís better than the one provided, please feel free to share if you would like to. No obligation, just fellow DBAs sharing tips and knowledge.

Tuesday, June 1, 2021 - 4:28:01 PM - Jeff Moden Back To Top (88787)
p.s. I also usually use a million rows for such tests because most people now have even reference tables bigger than 100K rows.


download





Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

How to use @@ROWCOUNT in SQL Server

SQL Server Loop through Table Rows without Cursor














get free sql tips
agree to terms