SQL - CAST() Function

---

---

The primary purpose of the SQL CAST() function is to transform an expression from one data type to another. This function produces an error if the SQL Server CAST function is unable to convert a declaration to the desired data type. With the CAST function, we can transform numerical data into character or string data.

The CAST() function either truncates or rounds off the result to the higher or lower value when converting data types with different decimal places.

Syntax

Following is the syntax for SQL CAST() function −

CAST(expression AS datatype(length))

Parameters

This function accepts only three parameter. The same is described below −

• expression − the valid expression that to be converted.

• datatype − It is the datatype that we want to convert the expression to.

• length − It was used to define the length of any targeted data type, as it was an optional parameter of an integer type.

Example

Let us try to convert the value to an int data type by using the following query −

SELECT DAY('2023/02/16') AS DayOfMonth;

Output

When we execute the above query, the output is obtained as follows −

+--------------------------+
|              intdatatype |
+--------------------------+
|                       20 |
+--------------------------+

Example

Let us convert the value into varchar datatype by using the following query −

SELECT CAST(20.23 AS varchar) AS varchardatatype;

Output

On executing the above query, the output is displayed as follows −

+--------------------------+
|          varchardatatype |
+--------------------------+
|                    20.23 |
+--------------------------+

Example

In the following example we are going to convert the value into the datetime datatype by using the following query −

SELECT CAST('2023-02-22' AS datetime) AS datetimedatatype; 

Output

The output for the above query is produced as given below −

+--------------------------+
|         datetimedatatype |
+--------------------------+
|  2023-02-22 00:00:00.000 |
+--------------------------+ 

Example

Look at the following example, where we are going to convert the single Boolean value of data type Bit by using the following query −

SELECT CAST('0' AS BIT) As BitFalse,
   CAST('1' AS BIT) As BitTrue;

Output

If we compile and run the above query, the result is produced as follows −

+-------------------+----------------+
|          BitFalse |    BitTrue     |
+-------------------+----------------+
|             0     |              1 |
+-------------------+----------------+

Example

Considering the following example, where we are using the CAST() function for converting character of string to character of length() by using the following query −

SELECT CAST('TUTORIALSPOINT' AS char(9)) AS Result;

Output

On executing the above query, it will generate the following output as shown below −

+------------------------------------+
|                              Result|
+------------------------------------+
|                          TUTORIALS |
+------------------------------------+

Example

Let’s consider another example, where we are going to cast the NULL value and checking the result by running the following query −

SELECT CAST(NULL AS DATE) AS Result;

Output

When the query gets executed, it will generate the output as shown below −

+------------------------------------+
|                              Result|
+------------------------------------+
|                                NULL|
+------------------------------------+

Example

Let us create a table with the name Studentmarks in the SQL database using the CREATE statement as shown in the query below−

CREATE TABLE Studentmarks(
ID  INT,
NAME VARCHAR(220),
SCORE FLOAT);

Now, let us insert some records in the Studentmarks table using INSERT statements as shown in the query below −

INSERT INTO Studentmarks VALUES (1, 'Shikhar','80.45');
INSERT INTO Studentmarks VALUES (2, 'Suresh','56.47');
INSERT INTO Studentmarks VALUES (3, 'Rana','23.89');
INSERT INTO Studentmarks VALUES (4, 'Ram','66.77');

Let’s verify whether the table Studentmarks is created or not using the following query −

SELECT * FROM Studentmarks;

The table was created in the database and displayed as shown below −

+------+---------+-------+
| ID   | NAME    | SCORE |
+------+---------+-------+
|    1 | Shikhar | 80.45 |
|    2 | Suresh  | 56.47 |
|    3 | Rana    | 23.89 |
|    4 | Ram     | 66.77 |
+------+---------+-------+

Now we are going to use the CAST() function for the conversion of float type to integer type of score column by using the following query −

SELECT NAME,CAST(SCORE AS Integer)Int_Score FROM Studentmarks;

Output

When we execute the above query, the output is obtained as follows −

+------------+---------------+
| NAME       |     int_Score |
+------------+---------------+
| Shikhar    |             80| 
| Suresh     |             56| 
| Rana       |             23| 
| Ram        |             66| 
+------------+---------------+