By: Aubrey Love | Updated: 2021-05-25 | Comments (2) | Related: > Functions System
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:
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.
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:
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:
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:
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:
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
CONVERT
PARSE
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
- Further reading on the CAST function.
About the author
Aubrey Love is a self-taught DBA with more than six years of experience designing, creating, and monitoring SQL Server databases as a DBA/Business Intelligence Specialist. Certificates include MCSA, A+, Linux+, and Google Map Tools with 40+ years in the computer industry. Aubrey first started working on PC’s when they were introduced to the public in the late 70's.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-05-25
