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 type 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 into MS SQL DateTime format.

SELECT CAST('2017-08-25 12:05:34.789' AS datetime);
Cast String To DateTime
Cast String To 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 Date
Cast String To 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 String To Time 
Cast String To 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 Number To Character
Cast Number To Character

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 Number
Cast String To 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);
Cast String To Floating Point
Cast String To Floating Point

Leave a Comment