SQL CAST() function – All you need to know!

Hey, folks! In this article, we will be focusing on the SQL CAST() function in detail. So, let us begin!

Working of SQL CAST() function?

SQL, like other programming languages, contains various functions to deal with different types of input data from the user.

One such function is CAST() function.

The CAST() function alters and converts the data type of a particular value to a desired data type. By this, any input value of the database can be manipulated to the desired data type in accordance to the need of the administration.

Now, let us understand the syntax of CAST() function-

CAST(value AS data-type);

The CAST() function can convert the values to any of the following data types

• TIME
• CHAR
• UNSIGNED
• SIGNED
• BINARY
• DATE
• DATETIME

Let us now implement the CAST() function through some examples in the upcoming section.

Examples of CAST() function

In the below example, we have altered the value of type integer to character data type.

SELECT CAST(100 AS CHAR);

Output:

CAST(100 AS CHAR)
100

Further, we have converted the value of type date to date time type as shown below–

SELECT CAST("2010-10-10" AS DateTIME);

Output:

CAST("2010-10-10" AS DateTIME)
2010-10-09T22:00:00.000Z

Now, we have converted the output of the subtraction performed into signed data type as shown below–

SELECT CAST(1-5 AS SIGNED);  

As seen below, 1 – 5 would lead to -4 because of being converted to signed data type.

Output:

CAST(1-5 AS SIGNED)
-4

SQL CAST() v/s CONVERT() function

The main and noticeable distinction between CAST() and CONVERT() function is as follows-

CAST() function does manipulate the data types without following the specific format. On the other hand, the CONVERT() function does conversion and formatting of the data values simultaneously with the help of an optional parameter ‘style‘.

Thus, CONVERT() function has an upper hand in terms of formatting the data values.

Conclusion

By this, we have come to the end of this topic. Feel free to comment below, in case you come across any question. For more such topics related to SQL, do visit SQL with JournalDev.

Till then, Happy Learning!!

References

• SQL CAST() Function- Documentation