SQL CAST() Function Tutorial – Cast and Convert A Value To A Specified Data Type In SQL

MS SQL Server provides some functions in order to make database administrators’ life easier. A typical database store different types of values. In some cases, we may need to cast and convert these values into different data types. MS SQL provides CAST() function can be used to change data types.

CAST() Function Syntax

CAST() function has a simple syntax where we will provide the value and the type we want to cast to.

CAST(expression AS datatype(length))

• expression is the value, variable, or expression we want to convert.
• datatype is the data type we want to convert to.
• length is optional where varchar or similar data types require length for the variable storage.

Cast String To DateTime

Date and times can be expressed in string format but MS SQL also provides DateTime date type which provides special functions about date and time. In this example, we will provide some data in string format and convert it into MS SQL DateTime format.

SELECT CAST('2017-08-25 12:05:34.789' AS datetime);

We can see from the screenshot that the date which consists of year, month, day and time which also consist of the hour, minute, second and millisecond converted into datetime type.

Cast String To Date

In the previous example, we have converted a string into DateTime format which is a compound type for date and time. We can also convert or cast just the string into a date.

SELECT CAST('2017-08-25' AS date);

Cast String To Time

We can also just cast or convert a string into a time which contains hour, minute, second, a millisecond.

SELECT CAST('12:13:14.567' AS time);

Cast Number To Character

We can cast a number into a character or character array/string. We will use char data type in this example but other char types like nchar, varchar can be also used.

SELECT CAST( 67 AS char);

Cast String To Number

We can also provide numbers in string format and convert them into number data type easily.

SELECT CAST( '123' AS number);

Cast String To Floating Point

number data type only stores integers and floating points can not be stored. We can cast a string into a floating-point number with the float data type.

SELECT CAST( '123.456' AS float);