The SQL Server CAST Function explicitly converts an expression from one data type to another. While working with a database, data transformation, such as changing data type from one to another, is one of the important roles. By changing the data type, you can perform calculations, comparisons, and other operations.
For instance, when you want to convert a string to an integer, decimal, or date and time, or vice versa, the SQL CAST function is the best option. If it is unable to convert an expression into the desired data type, this function will return an Error.
SQL CAST Function Syntax
The syntax of the CAST Function to explicitly convert any value from one data type to another is as shown below.
CAST(Expression AS Data_Type [(Length)])
For the sake of reference, let me provide an example to illustrate how to convert a number to a string.
SELECT CAST(123 AS VARCHAR(50)) AS [result_name]
FROM [Source]
The following are the SQL Server CAST function arguments:
- Expression: A Valid expression you want to convert into the desired data type. It can be a simple expression or column value.
- Data_Type: Specify the Data Type to which you want to convert the given expression. The list of supported data types that can be passed as this argument is: bit, tinyint, smallint, int, bigint, numeric, decimal, float, real, smallmoney, money, smalldatetime, datetime, char, nchar, varchar, nvarchar, text, ntext, varbinary, or image.
- Length: This is an optional parameter of integer type. Used to define the length of the target data types, such as VARCHAR, CHAR, etc. By default, it is 30.
The SQL CAST function converts any valid expression to the desired type. The following set of examples converts a string to an integer, decimal, or date and time. We also cover the handling of NULL values and the CAST function for DML operations.
TIP: Converting an empty string to VARCHAR returns an empty string, whereas converting to INT returns zero.
SQL CAST AS NUMBER
Suppose we have a table column with CHAR, VARCHAR, or NVARCHAR data type containing integer values, such as 123, 1234, 456, and so on. In such a case, use the CAST function to convert a string value to an integer.
First, we declared a variable of the VARCHAR type. Next, we were assigned the string data ‘12121’. Next, we use the CAST function to convert the string value to an integer. We also assigned a new name, ‘StrToInt’, using the ALIAS column in SQL Server. In the next line, we directly used the string number inside the method.
DECLARE @str AS VARCHAR(50)
SET @str = '12121'
SELECT CAST(@str AS INT) AS StrToInt;
SELECT CAST('1144' AS INT) AS New
The result set of the above query is:
StrToInt
-----------
12121
New
-----------
1144Convert a String to an Integer to perform Arithmetic Operations
Although the VARCHAR column stores integer values, when displayed in a table, it may not show much difference. However, it is not possible to perform any calculation on them for further data analysis. For instance, to perform arithmetic operations, using the +, -, *, etc operators, both operands should be of numeric type, so you need the SQL Server CAST function.
The statements below use two CAST functions, one for converting a string to an integer and the other for converting a decimal. Here, the first one is for addition and the second for multiplication.
SELECT CAST('10' AS INT) + CAST('15' AS DECIMAL(10,2)) AS Tot
SELECT CAST('10' AS INT) * CAST('5.6' AS DECIMAL(10,2)) AS Mul
Tot
---------
25.00
Mul
--------
56.00The statement below adds two integer values and converts them to money. Here, the format function adds the $ symbol at the beginning.
SELECT FORMAT(CAST((100 + 200) AS MONEY), '$#,##0.00') AS [Sum];
Sum
----------
$300.00Casting an Integer to BIT Value
If you have any bit values stored as an integer or string text, use the CAST function to convert them back to bits. Here, 0 is FALSE and 1 is TRUE.
SELECT CAST('1' AS BIT) AS b1;
SELECT CAST(0 AS BIT) AS b2;
b1
-----
1
b2
---
0SQL CAST function from Number to VARCHAR
In the above example, we have shown the CAST function to convert a string to an integer, and this section does the opposite, changing numbers to CHAR, VARCHAR, or NVARCHAR, etc. When you perform string concatenation, combining a string and a number, casting plays a vital role in changing numbers to text.
SELECT CAST(12345 AS VARCHAR(20)) AS nv1;
SELECT CAST(789 AS CHAR(10)) AS nv2;
nv1
-----------
12345
nv2
----------
789 The CAST function example below converts the decimal value to a string.
SELECT CAST(1312.485 AS VARCHAR(10)) AS dv1;
SELECT CAST(456.876 AS CHAR(10)) AS dv2;
dv1
----------
1312.485
dv2
----------
456.876 Apart from the above, if there is any VARCHAR column with a combination of number and string, and you want to separate the number to perform further investigation. In such a case, use the SQL CAST function to convert that VARCHAR column to an INT or integer data type.
Here, we used a simple LEFT function to extract the first three digits and the CAST function to convert it to an integer. In real time, you might have to use multiple built-in functions to extract numbers, but the CAST functionality is the same.
SELECT CAST(LEFT('897ABCDE', 3) AS INT) AS Result
Result
-----------
897Converting Binary Data type to Varchar
The SQL CAST function is also helpful for converting the binary values to any other supporting data types. The following example uses the binary value and converts it to a VARCHAR string. In the example below, 0x48656c6c6f20576f726c64 is the hex representation of the ASCII string Hello World. Next, 0x5475746f7269616c2047617465776179 represents “Tutorial Gateway”.
SELECT CAST(0x48656c6c6f20576f726c64 AS VARCHAR(50)) AS bv1;
SELECT CAST(0x5475746f7269616c2047617465776179 AS VARCHAR(50)) AS bv2;
bv1
-----------------------
Hello World
bv2
----------------------
Tutorial GatewaySQL CAST AS DECIMAL
In this example, we use multiple statements to show all options for converting a given data type to a decimal. The first line will convert the string value to a decimal with two decimal places. The third one converts the integer value to a decimal precision of three.
SELECT CAST('1234' AS DECIMAL(10, 2)) AS sd1;
SELECT CAST('1234.69' AS DECIMAL(10, 2)) AS sd2;
SELECT CAST(12345 AS DECIMAL(10, 3)) AS sd3;
sd1
--------------
1234.00
sd2
---------------
1234.69
sd3
---------------
12345.000Converting DECIMAL to INT
In this example, we used the SQL CAST function directly on the decimal value 123.456 and converted it to an integer (from higher to lower). Here, it will truncate the decimal values and return 123. I suggest you refer to Data Types to understand the data types and their limitations.
SELECT CAST(123.456 AS INT) AS di1;
di1
-------
123How do I CAST to 2 decimal places in SQL?
Casting a given value to 2 decimal places is the most common question you might find on internet forums. Whether you pass an integer, a numeric string, or a decimal value to the CAST function, it will only consider the DECIMAL(10, 2) as the return type. Here, DECIMAL(10, 2) means a number with 2 decimal places. So, if there are any values after the decimal, it keeps the first two and removes the other. If there are none, it adds the default 00 as the two decimal places.
SELECT CAST(4567 AS DECIMAL(10, 2)) AS cd1;
SELECT CAST('1234.678' AS DECIMAL(10, 2)) AS cd2;
SELECT CAST('134.67845' AS DECIMAL(10, 2)) AS cd3;
SELECT CAST(1148.8745 AS DECIMAL(10, 2)) AS cd4;
cd1
------------
4567.00
cd2
-------------
1234.68
cd3
-----------
134.68
cd4
-----------
1148.87Truncating Values
From the above SQL CAST function example, you can understand how the extra decimal values are truncated based on the given precision value. To demonstrate, we used various decimal precision values from 0 to 3. However, you can experiment with other values.
SELECT CAST(1234.2457 AS DECIMAL(10, 0)) AS dt1
SELECT CAST(1234.2457 AS DECIMAL(10, 1)) AS dt2
SELECT CAST(1234.2457 AS DECIMAL(10, 2)) AS dt3
SELECT CAST('134.67845' AS DECIMAL(10, 3)) AS dt4;
dt1
------------
1234
dt2
-------------
1234.2
dt3
-------------
1234.25
dt4
-------------
134.678SQL CAST AS DATE and DATETIME
When you are preparing a report or performing data analysis, converting a string value to a date/datetime is the most common scenario. In most cases, the data coming from Excel or text files contains the date as a string. A few people cast while performing the insertion, and some may do it while preparing reports.
For instance, we have a ’07/03/2017′ or VARCHAR type, and the format is dd/mm/yyyy. Here, you can use the CAST function to convert a VARCHAR string value to DATETIME or DATE. The statement below converts the string value to a date.
SELECT CAST('2025-07-03' AS DATE) AS sd1;
SELECT CAST('01-APRIL-2025' AS DATE) AS sd2;
sd1
----------
2025-07-03
sd2
----------
2025-04-01Similarly, the following two examples convert the string value to a date and time.
SELECT CAST('07/03/2017' AS DATETIME) AS sd3;
SELECT CAST('07/03/2017' AS DATETIME2) AS sd4;
sd3
-----------------------
2017-07-03 00:00:00.000
sd4
---------------------------
2017-07-03 00:00:00.0000000Using the SQL CAST function with other date functions
Once you convert the string value to a date and time, you can use the other date functions to extract values from it or alter the values, such as predicting the past and future. To demonstrate the same, we used the DATEADD function to add 20 days and subtract 18 days. Please experiment with DAY, YEAR, DATEDIFF functions, etc.
SELECT DATEADD(day, 20, (CAST('2025-07-25' AS DATE))) AS future;
SELECT DATEADD(day, -18, (CAST('2025-07-25' AS DATE))) AS past;
future
----------
2025-08-14
past
----------
2025-07-07Truncation date and time
When casting a string value to a date and time, you must be careful with the chosen data type; otherwise, values will be truncated automatically.
Although the string value has a date and time with milliseconds, the SQL CAST function returns the date part because we used the DATE datatype.
SELECT CAST('2025-07-03 10:30:55.123456' AS DATE) AS sdt1;
sdt1
----------
2025-07-03Similarly, it reruns the TIME only.
SELECT CAST('2025-07-03 10:30:55.123456' AS TIME) AS sdt2;
sdt2
----------------
10:30:55.1234560Casting Today’s Date
Casting today’s date to time and varchar is one of the most commonly asked questions in the forums, and the SQL CAST function works perfectly for this. Here, the GETDATE() function returns today’s datetime, and the CAST function converts it to date, time, and string.
SELECT CAST(GETDATE() AS DATE) AS ds1;
SELECT CAST(GETDATE() AS TIME) AS ds2;
SELECT CAST(GETDATE() AS VARCHAR(50)) AS ds3;
ds1
----------
2025-09-23
ds2
----------------
09:54:29.7233333
ds3
------------------------
Sep 23 2025 9:54AMConverting DATE to TIMESTAMP
The SQL CAST function is also helpful in converting a string or date value to a TIMESTAMP. When you execute the query, it sets the time part as 00:00:00.000000, adds it to the date part, and converts the whole value into TIMESTAMP.
SELECT CAST('2025-07-06' AS TIMESTAMP) AS ts;
ts
------------------
0x323032352D30372DCasting Date to String
This example accepts today’s date and time as input, converts to a string, and returns the output.
SELECT CAST(GETDATE() AS CHAR(12)) AS d1;
SELECT CAST(GETDATE() AS VARCHAR(50)) AS d2;
d1
------------
Sep 23 2025
d2
--------------------------
Sep 23 2025 9:53AMSQL CAST function on Table Columns
This section uses the table data to show the CAST function in SELECT, INSERT, UPDATE, and WHERE clauses.
We will apply the CAST function to our Employee table in this example. Here, we change the integer Income to Decimal, Decimal Sales to Integer, and the HireDate column to DATETIME2 data type.
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,CAST([YearlyIncome] AS DECIMAL(10, 3)) AS [Income]
,CAST([Sales] AS INT) AS [Amount]
,CAST([HireDate] AS DATETIME2) AS [Date]
FROM [Employee]
INSERT Example
Imagine you are importing the employee records from an Excel sheet or a text file where the datetime values are enclosed in single quotes. When you insert the same, the server will throw an error. To resolve this, you must use the CAST function before inserting. To demonstrate, we will insert a single record into the above table.
INSERT INTO [dbo].[Employee] ([FirstName],[LastName],[Education]
,[Occupation],[YearlyIncome],[Sales],[HireDate])
VALUES('Tim','Cook', 'Msc','CEO', 123456789, 234567.76, CAST('2016-05-01' AS DATETIME))
Use the SQL CAST function in the UPDATE statement
If your table already has the date and time values as VARCHAR, use the CAST function to update them as the datetime values before converting the whole column.
UPDATE Employee
SET HireDate = CAST('2016-05-01' AS DATETIME))
WHERE EmpID = 15
WHERE clause
The HireDate in the above table is a datetime column. However, imagine it is a string column and you want to see the employees who were hired after 2013. In this case, within the WHERE clause, use the CAST function to convert the VARCHAR to DATE and the YEAR function to extract the year number.
SELECT * FROM Employee
WHERE YEAR(CAST(HireDate AS Date)) > 2013
EmpID FirstName LastName Education Occupation YearlyIncome Sales HireDate
10 Christy Carlson Graduate Degree Management 70000 2234.99 2014-01-25 16:14:14.110
12 Barry Johnson Education Management 80000 4968.59 2014-05-15 05:03:10.157Common SQL CAST function Mistakes and Fix Them!
The CAST function is one of the powerful methods for data analysis and reporting, but overusing it will impact the query performance. The following are some common mistakes that most people make while working with the CAST function.
Handling NULL values
It considers the NULL values as the same and does nothing to them. When you convert a string to a date, the NULL values will be treated as non-existent items. In this example, we used the SQL Server CAST function to work with NULL values as integers.
DECLARE @str AS VARCHAR(50)
SET @str = NULL
SELECT CAST(@str AS INT) AS n1;
SELECT CAST(NULL AS DECIMAL(10,2)) AS n2;
n1
-----------
NULL
n2
-------------
NULLLoss of text
When you cast a string data to a lower data type (VARCHAR to CHAR), you might lose the text. It happens even when the determined length is lower than the incoming characters. In both statements below, the length does not match the total characters in a given string.
SELECT CAST('Hello World' AS CHAR(5)) AS st1;
SELECT CAST('Hello World' AS VARCHAR(9)) AS st2;
st1
-----
Hello
st2
---------
Hello WorDecimal Points Truncating
When converting from a float or string to decimal points, the decimal precision is very important. For the data analysis, each value plays a crucial role, so you must be cautious when defining the decimal points.
The first SQL CAST function line truncates the decimal points to 2, which might be wrong in most cases. The second and third ones are the correct approach. However, based on the data analysis, there will be a cut-off point. If the decimals are crucial, keep everything; otherwise, round them to the required number.
SELECT CAST(72134.22457 AS DECIMAL(10, 2)) AS ed0
SELECT CAST(72134.22457 AS DECIMAL(10, 5)) AS ed1
SELECT CAST('1934.7845' AS DECIMAL(10, 4)) AS ed2;
ed0
----------------
72134.22
ed1
-----------------
72134.22457
ed2
----------------
1934.7845Arithmetic Overflow
When you try to fit the four-digit number in a three-digit value, the SQL CAST function will throw an arithmetic overflow. To fix this error, change DECIMAL(3,1) to DECIMAL(4,1). However, DECIMAL(10, 2) is safe.
SELECT CAST(2243.24 AS DECIMAL(3,1)) AS e2
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.It happens when you downgrade from a larger range data type to a smaller range data type. For instance, casting BIGINT to INT will also return the same arithmetic overflow. The list goes on for all data types from highest to lowest. So, always choose the correct data type.
SELECT CAST(1112234567789 AS INT) AS e3
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.To fix the above error, use BIGINT.
SELECT CAST(1112234567789 AS BIGINT) AS e4
e4
--------------------
1112234567789Implicit and Explicit Conversion
The SQL Server can implicitly convert the lower-preceding data type to higher-preceding ones. It may sometimes give a strange result and throw errors. For instance, the code below implicitly converts ’10’ to an integer and performs addition.
SELECT '10' + 20 AS e5;
e5
-------
30So, you don’t need the code below to convert explicitly.
SELECT CAST('10' AS INT) + 20 AS e6;
e6
---------
30Based on the above example, if you use the same logic for the code below, it throws an error. And the error is: Conversion failed when converting the varchar value ‘Hello ‘ to data type int
SELECT 'Hello ' + 20 AS e7;
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Hello ' to data type int.Here, you have to convert 20 explicitly to a string and for this use the SQL CAST function.
SELECT 'Hello ' + CAST(20 AS VARCHAR(10)) AS e8;
e8
-----------
Hello 20Let us use what happens if we try to convert the ‘Tutorial Gateway’ string to an integer. It is not possible, so the CAST function returned an Error as output.
SELECT CAST('Tutorial Gateway' AS INT) AS e9;
Execute the above query and see the error message.
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the Varchar value 'Tutorial Gateway' to data type int.To fix the above error, change the data type from INT to VARCHAR or CHAR.
SELECT CAST('Tutorial Gateway' AS VARCHAR(20)) AS e10;
e10
--------------------
Tutorial GatewayTIP: Use the TRY_CAST function to avoid errors because it returns NULL instead of throwing an error.
Comments are closed.